3403 lines
201 KiB
C#
3403 lines
201 KiB
C#
using System.Security.Claims;
|
||
using BMA.EHR.Domain.Models.Placement;
|
||
using BMA.EHR.Recurit.Exam.Service.Extensions;
|
||
using BMA.EHR.Recurit.Exam.Service.Core;
|
||
using BMA.EHR.Recurit.Exam.Service.Data;
|
||
using BMA.EHR.Recurit.Exam.Service.Models;
|
||
using BMA.EHR.Recurit.Exam.Service.Request;
|
||
using BMA.EHR.Recurit.Exam.Service.Response;
|
||
using BMA.EHR.Recurit.Exam.Service.Responses.Document;
|
||
using Microsoft.EntityFrameworkCore;
|
||
using OfficeOpenXml;
|
||
using OfficeOpenXml.Style;
|
||
using System.Text;
|
||
using Newtonsoft.Json;
|
||
using System.Net.Http.Headers;
|
||
|
||
namespace BMA.EHR.Recurit.Exam.Service.Services
|
||
{
|
||
public class PeriodExamService
|
||
{
|
||
#region " Fields "
|
||
|
||
private readonly ApplicationDbContext _context;
|
||
private readonly MetadataDbContext _contextMetadata;
|
||
private readonly OrgDbContext _contextOrg;
|
||
private readonly IHttpContextAccessor _httpContextAccessor;
|
||
private readonly MinIOService _minioService;
|
||
private readonly MailService _mailService;
|
||
private readonly IConfiguration _configuration;
|
||
private readonly IHttpClientFactory _httpClientFactory;
|
||
|
||
#endregion
|
||
|
||
#region " Constructor and Destructor "
|
||
|
||
public PeriodExamService(ApplicationDbContext context,
|
||
MetadataDbContext contextMetadata,
|
||
OrgDbContext contextOrg,
|
||
IHttpContextAccessor httpContextAccessor,
|
||
MinIOService minioService,
|
||
MailService mailService,
|
||
IConfiguration configuration,
|
||
IHttpClientFactory httpClientFactory)
|
||
{
|
||
_context = context;
|
||
_contextMetadata = contextMetadata;
|
||
_contextOrg = contextOrg;
|
||
_httpContextAccessor = httpContextAccessor;
|
||
_minioService = minioService;
|
||
_mailService = mailService;
|
||
_configuration = configuration;
|
||
_httpClientFactory = httpClientFactory;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region " Properties "
|
||
|
||
private string? UserId => _httpContextAccessor?.HttpContext?.User?.FindFirst(ClaimTypes.NameIdentifier)?.Value;
|
||
|
||
private string? FullName => _httpContextAccessor?.HttpContext?.User?.FindFirst("name")?.Value;
|
||
private string? token => _httpContextAccessor?.HttpContext?.Request.Headers["Authorization"];
|
||
|
||
#endregion
|
||
|
||
#region " Methods "
|
||
|
||
private string GenerateStatusCandidate(string status)
|
||
{
|
||
switch (status.Trim().ToUpper())
|
||
{
|
||
case "REGISTER": return "รอยืนยันสมัครสอบ";
|
||
case "CHECKREGISTER": return "ตรวจสอบข้อมูลสมัครสอบ";
|
||
case "PAYMENT": return "รอชำระค่าสมัครสอบ";
|
||
case "REJECTREGISTER": return "ปฏิเสธตรวจสอบข้อมูลสมัคร";
|
||
case "CHECKPAYMENT": return "ตรวจสอบหลักฐานชำระเงิน";
|
||
case "REJECTPAYMENT": return "หลักฐานชำระเงินไม่ถูกต้อง";
|
||
case "CHECKSEAT": return "จัดที่นั่งสอบ";
|
||
case "CHECKPOINT": return "สรุปคะแนนสอบ";
|
||
case "DONE": return "สอบคัดเลือกสำเร็จ";
|
||
case "WAIVER": return "สละสิทธิ์สอบ";
|
||
default: return status;
|
||
}
|
||
}
|
||
|
||
private string GenerateStatusOccupation(string status)
|
||
{
|
||
switch (status.Trim().ToUpper())
|
||
{
|
||
case "PREM": return "ลูกจ้างประจำ";
|
||
case "TEMP": return "ลูกจ้างชั่วคราว";
|
||
case "OTHER": return "ผู้ปฏิบัติงานอื่นในกรุงเทพมหานคร";
|
||
default: return status;
|
||
}
|
||
}
|
||
|
||
public async Task<IEnumerable<PeriodExamCandidateResponseItem>> GetsAsync(string type, bool showAll = true)
|
||
{
|
||
var periodExams = await _context.PeriodExams.AsQueryable()
|
||
.Where(p => p.IsActive)
|
||
.Where(p => p.CheckDisability == false)
|
||
.Where(p => type.ToUpper() == "ALL" ? (p.AnnouncementExam == true || p.AnnouncementExam == false) : (type.ToUpper() == "EXAM" ? p.AnnouncementExam == true : p.AnnouncementExam == false))
|
||
.OrderByDescending(d => d.CreatedAt).ToListAsync();
|
||
var _periodExams = (from x in periodExams
|
||
select new PeriodExamCandidateResponseItem
|
||
{
|
||
ExamDate = x.ExamDate,
|
||
AnnouncementEndDate = x.AnnouncementEndDate,
|
||
AnnouncementStartDate = x.AnnouncementStartDate,
|
||
AnnouncementDate = x.AnnouncementDate,
|
||
CheckDisability = x.CheckDisability,
|
||
CheckDocument = x.CheckDocument,
|
||
Detail = x.Detail,
|
||
EditorCondition = x.EditorCondition,
|
||
EditorConfirm = x.EditorConfirm,
|
||
Fee = x.Fee,
|
||
Id = x.Id,
|
||
IsActive = x.IsActive,
|
||
Name = x.Name,
|
||
Note = x.Note,
|
||
Remark = x.Remark,
|
||
CompanyCode = x.CompanyCode,
|
||
Reason = x.Reason,
|
||
RefNo1 = x.RefNo1,
|
||
OrganizationCodeId = x.OrganizationCodeId,
|
||
OrganizationCodeName = x.OrganizationCodeName,
|
||
OrganizationId = x.OrganizationId,
|
||
OrganizationName = x.OrganizationName,
|
||
PaymentEndDate = x.PaymentEndDate,
|
||
PaymentKrungThai = x.PaymentKrungThai,
|
||
AnnouncementExam = x.AnnouncementExam,
|
||
Category = x.Category,
|
||
PaymentStartDate = x.PaymentStartDate,
|
||
RegisterEndDate = x.RegisterEndDate,
|
||
RegisterStartDate = x.RegisterStartDate,
|
||
Round = x.Round,
|
||
SetSeat = x.SetSeat,
|
||
Year = x.Year,
|
||
CreatedUserId = x.CreatedUserId,
|
||
}).AsQueryable()
|
||
.ToList();
|
||
|
||
var roles = _httpContextAccessor?.HttpContext?.User?.FindAll(ClaimTypes.Role)?.Select(c => c.Value).ToList();
|
||
return _periodExams;
|
||
}
|
||
|
||
public async Task<PeriodExamCandidateResponseItem?> GetsExamAndCandidateAsync(string examId, bool showAll = true)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.Include(x => x.PositionExam)
|
||
.Include(x => x.BankExam)
|
||
.Include(x => x.PeriodExamDocuments)
|
||
.Select(x => new PeriodExamCandidateResponseItem
|
||
{
|
||
ExamDate = x.ExamDate,
|
||
AnnouncementEndDate = x.AnnouncementEndDate,
|
||
AnnouncementStartDate = x.AnnouncementStartDate,
|
||
AnnouncementDate = x.AnnouncementDate,
|
||
CheckDisability = x.CheckDisability,
|
||
CheckDocument = x.CheckDocument,
|
||
Detail = x.Detail,
|
||
EditorCondition = x.EditorCondition,
|
||
EditorConfirm = x.EditorConfirm,
|
||
Fee = x.Fee,
|
||
Id = x.Id,
|
||
IsActive = x.IsActive,
|
||
Name = x.Name,
|
||
Note = x.Note,
|
||
Remark = x.Remark,
|
||
CompanyCode = x.CompanyCode,
|
||
Reason = x.Reason,
|
||
RefNo1 = x.RefNo1,
|
||
OrganizationCodeId = x.OrganizationCodeId,
|
||
OrganizationCodeName = x.OrganizationCodeName,
|
||
OrganizationId = x.OrganizationId,
|
||
OrganizationName = x.OrganizationName,
|
||
PaymentEndDate = x.PaymentEndDate,
|
||
PaymentKrungThai = x.PaymentKrungThai,
|
||
AnnouncementExam = x.AnnouncementExam,
|
||
Category = x.Category,
|
||
PaymentStartDate = x.PaymentStartDate,
|
||
RegisterEndDate = x.RegisterEndDate,
|
||
RegisterStartDate = x.RegisterStartDate,
|
||
Round = x.Round,
|
||
SetSeat = x.SetSeat,
|
||
Year = x.Year,
|
||
GraduationYearLock = x.GraduationYearLock,
|
||
BankExam = x.BankExam.OrderBy(o => o.CreatedAt).Select(b => new BankExam
|
||
{
|
||
Id = b.Id,
|
||
AccountName = b.AccountName,
|
||
AccountNumber = b.AccountNumber,
|
||
BankName = b.BankName,
|
||
}).ToList(),
|
||
PositionExam = x.PositionExam.OrderBy(o => o.CreatedAt).Select(b => new PositionExam
|
||
{
|
||
Id = b.Id,
|
||
TypeId = b.TypeId,
|
||
TypeName = b.TypeName,
|
||
PositionId = b.PositionId,
|
||
PositionName = b.PositionName,
|
||
PositionLevelId = b.PositionLevelId,
|
||
PositionLevelName = b.PositionLevelName,
|
||
HighDegree = b.HighDegree,
|
||
Code = b.Code,
|
||
EducationLevel = b.EducationLevel,
|
||
}).ToList(),
|
||
Documents = x.PeriodExamDocuments.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
|
||
{
|
||
Id = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
FileName = b.Document == null ? "" : b.Document.FileName,
|
||
FileSize = b.Document == null ? 0 : b.Document.FileSize,
|
||
FileType = b.Document == null ? "" : b.Document.FileType,
|
||
Detail = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
}).ToList(),
|
||
Images = x.PeriodExamImages.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
|
||
{
|
||
Id = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
FileName = b.Document == null ? "" : b.Document.FileName,
|
||
FileSize = b.Document == null ? 0 : b.Document.FileSize,
|
||
FileType = b.Document == null ? "" : b.Document.FileType,
|
||
Detail = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
}).ToList(),
|
||
BarCodes = x.PeriodExamBarCodes.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
|
||
{
|
||
Id = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
FileName = b.Document == null ? "" : b.Document.FileName,
|
||
FileSize = b.Document == null ? 0 : b.Document.FileSize,
|
||
FileType = b.Document == null ? "" : b.Document.FileType,
|
||
Detail = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
}).ToList(),
|
||
QrCodes = x.PeriodExamQrCodes.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
|
||
{
|
||
Id = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
FileName = b.Document == null ? "" : b.Document.FileName,
|
||
FileSize = b.Document == null ? 0 : b.Document.FileSize,
|
||
FileType = b.Document == null ? "" : b.Document.FileType,
|
||
Detail = b.Document == null ? "" : b.Document.Id.ToString(),
|
||
}).ToList(),
|
||
})
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var i = 0;
|
||
foreach (var item in periodExam.Images)
|
||
{
|
||
if (periodExam.Images[i].Detail != null && periodExam.Images[i].Detail != "")
|
||
periodExam.Images[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.Images[i].Detail)).Result;
|
||
i++;
|
||
}
|
||
i = 0;
|
||
foreach (var item in periodExam.Documents)
|
||
{
|
||
if (periodExam.Documents[i].Detail != null && periodExam.Documents[i].Detail != "")
|
||
periodExam.Documents[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.Documents[i].Detail)).Result;
|
||
i++;
|
||
}
|
||
i = 0;
|
||
foreach (var item in periodExam.QrCodes)
|
||
{
|
||
if (periodExam.QrCodes[i].Detail != null && periodExam.QrCodes[i].Detail != "")
|
||
periodExam.QrCodes[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.QrCodes[i].Detail)).Result;
|
||
i++;
|
||
}
|
||
i = 0;
|
||
foreach (var item in periodExam.BarCodes)
|
||
{
|
||
if (periodExam.BarCodes[i].Detail != null && periodExam.BarCodes[i].Detail != "")
|
||
periodExam.BarCodes[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.BarCodes[i].Detail)).Result;
|
||
i++;
|
||
}
|
||
|
||
return periodExam;
|
||
}
|
||
|
||
public async Task<RequestStatusExam?> GetsStatusPaymentAsync(string examId, bool showAll = true)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
return new RequestStatusExam
|
||
{
|
||
Name = periodExam.Name,
|
||
Round = periodExam.Round,
|
||
Year = periodExam.Year,
|
||
Status = periodExam.PaymentEndDate == null ? true : DateTime.Now > periodExam.PaymentEndDate,
|
||
SetSeat = periodExam.SetSeat,
|
||
Fee = periodExam.Fee,
|
||
};
|
||
}
|
||
|
||
public async Task<RequestPositionName?> GetsNamePositionAsync(string examId, string positionId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.Include(x => x.PositionExam)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var positionExam = await _context.PositionExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(positionId));
|
||
|
||
if (positionExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
if (positionId != "00000000-0000-0000-0000-000000000000")
|
||
{
|
||
return new RequestPositionName
|
||
{
|
||
Name = periodExam.Name,
|
||
Round = periodExam.Round,
|
||
Year = periodExam.Year,
|
||
Position = positionExam.PositionName,
|
||
PositionLevel = positionExam.PositionLevelName,
|
||
EducationLevel = positionExam.EducationLevel,
|
||
};
|
||
}
|
||
else
|
||
{
|
||
return new RequestPositionName
|
||
{
|
||
Name = periodExam.Name,
|
||
Round = periodExam.Round,
|
||
Year = periodExam.Year,
|
||
Position = null,
|
||
PositionLevel = null,
|
||
EducationLevel = positionExam.EducationLevel,
|
||
};
|
||
}
|
||
}
|
||
|
||
public async Task<Guid> CreateAsync(RequestPeriodExam inserted)
|
||
{
|
||
var periodExam = new PeriodExam
|
||
{
|
||
Name = inserted.Name,
|
||
CheckDocument = inserted.CheckDocument,
|
||
Round = inserted.Round,
|
||
Year = inserted.Year,
|
||
Fee = inserted.Fee,
|
||
RegisterStartDate = inserted.RegisterStartDate,
|
||
RegisterEndDate = inserted.RegisterEndDate,
|
||
PaymentStartDate = inserted.PaymentStartDate,
|
||
PaymentEndDate = inserted.PaymentEndDate,
|
||
AnnouncementStartDate = inserted.AnnouncementStartDate,
|
||
AnnouncementDate = inserted.AnnouncementDate,
|
||
ExamDate = inserted.ExamDate,
|
||
AnnouncementEndDate = inserted.AnnouncementEndDate,
|
||
OrganizationCodeId = inserted.OrganizationCodeId,
|
||
OrganizationCodeName = inserted.OrganizationCodeName,
|
||
OrganizationId = inserted.OrganizationId,
|
||
OrganizationName = inserted.OrganizationName,
|
||
PaymentKrungThai = inserted.PaymentKrungThai,
|
||
AnnouncementExam = inserted.AnnouncementExam,
|
||
Category = inserted.Category,
|
||
Detail = inserted.Detail,
|
||
EditorCondition = inserted.EditorCondition,
|
||
EditorConfirm = inserted.EditorConfirm,
|
||
Note = inserted.Note,
|
||
Remark = inserted.Remark,
|
||
CompanyCode = inserted.CompanyCode,
|
||
Reason = inserted.Reason,
|
||
RefNo1 = inserted.RefNo1,
|
||
GraduationYearLock = inserted.GraduationYearLock ?? 3,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
|
||
if (inserted.PaymentKrungThai == "payment2")
|
||
{
|
||
foreach (var bank in inserted.BankExam)
|
||
{
|
||
var bankExam = new BankExam
|
||
{
|
||
PeriodExam = periodExam,
|
||
AccountName = bank.AccountName,
|
||
AccountNumber = bank.AccountNumber,
|
||
BankName = bank.BankName,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _context.BankExams.AddAsync(bankExam);
|
||
}
|
||
}
|
||
|
||
foreach (var position in inserted.PositionExam)
|
||
{
|
||
var positionExam = new PositionExam
|
||
{
|
||
PeriodExam = periodExam,
|
||
PositionId = position.PositionId,
|
||
PositionName = position.PositionName,
|
||
PositionLevelId = position.PositionLevelId,
|
||
PositionLevelName = position.PositionLevelName,
|
||
HighDegree = position.HighDegree,
|
||
Code = position.Code,
|
||
TypeId = position.TypeId,
|
||
TypeName = position.TypeName,
|
||
EducationLevel = position.EducationLevel,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _context.PositionExams.AddAsync(positionExam);
|
||
}
|
||
|
||
await _context.PeriodExams.AddAsync(periodExam);
|
||
await _context.SaveChangesAsync();
|
||
|
||
return periodExam.Id;
|
||
}
|
||
|
||
public async Task UpdateAsync(string examId, RequestPeriodExam updated)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.Include(x => x.BankExam)
|
||
.Include(x => x.PositionExam)
|
||
.Include(x => x.Candidate)
|
||
.Include(x => x.PeriodExamDocuments)
|
||
.ThenInclude(x => x.Document)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
periodExam.Name = updated.Name;
|
||
periodExam.CheckDocument = updated.CheckDocument;
|
||
periodExam.Round = updated.Round;
|
||
periodExam.Year = updated.Year;
|
||
periodExam.Fee = updated.Fee;
|
||
periodExam.RegisterStartDate = updated.RegisterStartDate;
|
||
periodExam.RegisterEndDate = updated.RegisterEndDate;
|
||
periodExam.PaymentStartDate = updated.PaymentStartDate;
|
||
periodExam.PaymentEndDate = updated.PaymentEndDate;
|
||
periodExam.AnnouncementStartDate = updated.AnnouncementStartDate;
|
||
periodExam.AnnouncementDate = updated.AnnouncementDate;
|
||
periodExam.ExamDate = updated.ExamDate;
|
||
periodExam.AnnouncementEndDate = updated.AnnouncementEndDate;
|
||
periodExam.OrganizationCodeId = updated.OrganizationCodeId;
|
||
periodExam.OrganizationCodeName = updated.OrganizationCodeName;
|
||
periodExam.OrganizationId = updated.OrganizationId;
|
||
periodExam.OrganizationName = updated.OrganizationName;
|
||
periodExam.PaymentKrungThai = updated.PaymentKrungThai;
|
||
// periodExam.AnnouncementExam = updated.AnnouncementExam;
|
||
periodExam.Category = updated.Category;
|
||
periodExam.Detail = updated.Detail;
|
||
periodExam.EditorCondition = updated.EditorCondition;
|
||
periodExam.EditorConfirm = updated.EditorConfirm;
|
||
periodExam.Note = updated.Note;
|
||
periodExam.Remark = updated.Remark;
|
||
periodExam.CompanyCode = updated.CompanyCode;
|
||
periodExam.Reason = updated.Reason;
|
||
periodExam.RefNo1 = updated.RefNo1;
|
||
periodExam.GraduationYearLock = updated.GraduationYearLock;
|
||
periodExam.LastUpdatedAt = DateTime.Now;
|
||
periodExam.LastUpdateUserId = UserId ?? "";
|
||
periodExam.LastUpdateFullName = FullName ?? "";
|
||
|
||
if (updated.PaymentKrungThai == "payment2")
|
||
{
|
||
foreach (var bank in periodExam.BankExam)
|
||
{
|
||
var bankData = updated.BankExam
|
||
.FirstOrDefault(x => x.Id == bank.Id);
|
||
if (bankData != null)
|
||
{
|
||
bank.AccountName = bankData.AccountName;
|
||
bank.AccountNumber = bankData.AccountNumber;
|
||
bank.BankName = bankData.BankName;
|
||
bank.LastUpdatedAt = DateTime.Now;
|
||
bank.LastUpdateUserId = UserId ?? "";
|
||
bank.LastUpdateFullName = FullName ?? "";
|
||
}
|
||
else
|
||
{
|
||
var bankDelete = await _context.BankExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == bank.Id);
|
||
|
||
if (bankDelete != null)
|
||
_context.BankExams.Remove(bankDelete);
|
||
}
|
||
}
|
||
var bankAdd = updated.BankExam
|
||
.Where(x => x.Id == Guid.Parse("00000000-0000-0000-0000-000000000000"))
|
||
.ToList();
|
||
foreach (var bank in bankAdd)
|
||
{
|
||
var bankExam = new BankExam
|
||
{
|
||
PeriodExam = periodExam,
|
||
AccountName = bank.AccountName,
|
||
AccountNumber = bank.AccountNumber,
|
||
BankName = bank.BankName,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _context.BankExams.AddAsync(bankExam);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
foreach (var bank in periodExam.BankExam)
|
||
{
|
||
var bankDelete = await _context.BankExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == bank.Id);
|
||
|
||
if (bankDelete != null)
|
||
_context.BankExams.Remove(bankDelete);
|
||
}
|
||
}
|
||
|
||
foreach (var position in periodExam.PositionExam)
|
||
{
|
||
var positionData = updated.PositionExam
|
||
.FirstOrDefault(x => x.Id == position.Id);
|
||
if (positionData != null)
|
||
{
|
||
position.PositionId = positionData.PositionId;
|
||
position.PositionName = positionData.PositionName;
|
||
position.PositionLevelId = positionData.PositionLevelId;
|
||
position.PositionLevelName = positionData.PositionLevelName;
|
||
position.HighDegree = positionData.HighDegree;
|
||
position.Code = positionData.Code;
|
||
position.TypeId = positionData.TypeId;
|
||
position.TypeName = positionData.TypeName;
|
||
position.EducationLevel = positionData.EducationLevel;
|
||
position.LastUpdatedAt = DateTime.Now;
|
||
position.LastUpdateUserId = UserId ?? "";
|
||
position.LastUpdateFullName = FullName ?? "";
|
||
}
|
||
else
|
||
{
|
||
var positionDelete = await _context.PositionExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == position.Id);
|
||
|
||
if (positionDelete != null)
|
||
{
|
||
if (periodExam.Candidate.Count() > 0)
|
||
throw new Exception("รอบนี้มีการสมัครแล้วไม่สามารถลบตำแหน่งได้");
|
||
_context.PositionExams.Remove(positionDelete);
|
||
}
|
||
}
|
||
}
|
||
|
||
var positionAdd = updated.PositionExam
|
||
.Where(x => x.Id == Guid.Parse("00000000-0000-0000-0000-000000000000"))
|
||
.ToList();
|
||
foreach (var position in positionAdd)
|
||
{
|
||
var positionExam = new PositionExam
|
||
{
|
||
PeriodExam = periodExam,
|
||
PositionId = position.PositionId,
|
||
PositionName = position.PositionName,
|
||
PositionLevelId = position.PositionLevelId,
|
||
PositionLevelName = position.PositionLevelName,
|
||
HighDegree = position.HighDegree,
|
||
Code = position.Code,
|
||
TypeId = position.TypeId,
|
||
TypeName = position.TypeName,
|
||
EducationLevel = position.EducationLevel,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _context.PositionExams.AddAsync(positionExam);
|
||
}
|
||
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UpdateDocAsync(string examId, IFormFileCollection files)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
// .Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
foreach (var file in files)
|
||
{
|
||
var doc = await _minioService.UploadFileAsync(file);
|
||
|
||
var document = await _context.Documents.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == doc.Id);
|
||
|
||
if (document == null)
|
||
throw new Exception(GlobalMessages.NoFileToUpload);
|
||
|
||
var periodExamDocument = new PeriodExamDocument
|
||
{
|
||
PeriodExam = periodExam,
|
||
Document = document,
|
||
};
|
||
|
||
await _context.PeriodExamDocuments.AddAsync(periodExamDocument);
|
||
}
|
||
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UpdateImgAsync(string examId, IFormFileCollection files)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
// .Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
foreach (var file in files)
|
||
{
|
||
var doc = await _minioService.UploadFileAsync(file);
|
||
|
||
var document = await _context.Documents.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == doc.Id);
|
||
|
||
if (document == null)
|
||
throw new Exception(GlobalMessages.NoFileToUpload);
|
||
|
||
var periodExamImage = new PeriodExamImage
|
||
{
|
||
PeriodExam = periodExam,
|
||
Document = document,
|
||
};
|
||
|
||
await _context.PeriodExamImages.AddAsync(periodExamImage);
|
||
}
|
||
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UpdateBarcodeAsync(string examId, IFormFileCollection files)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Include(x => x.PeriodExamBarCodes)
|
||
.ThenInclude(x => x.Document)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
if (periodExam.PeriodExamBarCodes != null && periodExam.PeriodExamBarCodes.Count() > 0 && periodExam.PeriodExamBarCodes[0] != null && periodExam.PeriodExamBarCodes[0].Document != null)
|
||
await _minioService.DeleteFileAsync(periodExam.PeriodExamBarCodes[0].Document.Id);
|
||
foreach (var file in files)
|
||
{
|
||
var doc = await _minioService.UploadFileAsync(file);
|
||
|
||
var document = await _context.Documents.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == doc.Id);
|
||
|
||
if (document == null)
|
||
throw new Exception(GlobalMessages.NoFileToUpload);
|
||
|
||
var periodExamBarCode = new PeriodExamBarCode
|
||
{
|
||
PeriodExam = periodExam,
|
||
Document = document,
|
||
};
|
||
|
||
await _context.PeriodExamBarCodes.AddAsync(periodExamBarCode);
|
||
}
|
||
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UpdateQrcodeAsync(string examId, IFormFileCollection files)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Include(x => x.PeriodExamQrCodes)
|
||
.ThenInclude(x => x.Document)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
if (periodExam.PeriodExamQrCodes != null && periodExam.PeriodExamQrCodes.Count() > 0 && periodExam.PeriodExamQrCodes[0] != null && periodExam.PeriodExamQrCodes[0].Document != null)
|
||
await _minioService.DeleteFileAsync(periodExam.PeriodExamQrCodes[0].Document.Id);
|
||
|
||
foreach (var file in files)
|
||
{
|
||
var doc = await _minioService.UploadFileAsync(file);
|
||
|
||
var document = await _context.Documents.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == doc.Id);
|
||
|
||
if (document == null)
|
||
throw new Exception(GlobalMessages.NoFileToUpload);
|
||
|
||
var periodExamQrCode = new PeriodExamQrCode
|
||
{
|
||
PeriodExam = periodExam,
|
||
Document = document,
|
||
};
|
||
|
||
await _context.PeriodExamQrCodes.AddAsync(periodExamQrCode);
|
||
}
|
||
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task DeleteDocument(string documentId)
|
||
{
|
||
await _minioService.DeleteFileAsync(Guid.Parse(documentId));
|
||
}
|
||
|
||
public async Task DeleteAsyncDocument(string examId, string documentId)
|
||
{
|
||
var exam = await _context.PeriodExams.AsQueryable()
|
||
// .Where(x => x.CheckDisability == false)
|
||
.Include(x => x.PositionExam)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (exam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
await _minioService.DeleteFileAsync(Guid.Parse(documentId));
|
||
}
|
||
|
||
public async Task DeleteAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
_context.PeriodExams.Remove(periodExam);
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task<dynamic> GetsCandidateByStatusAsync(string examId, string status, int page = 1, int pageSize = 25, string keyword = "")
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
if (status == "all")
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
.Include(x => x.ProfileImg)
|
||
.OrderByDescending(d => d.CreatedAt)
|
||
.Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
|
||
.Where(x =>
|
||
//(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
|
||
//(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
|
||
(x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
|
||
(x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
|
||
//(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
|
||
//(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
|
||
//(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
|
||
(x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
|
||
(x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
|
||
(x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
|
||
(x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
|
||
(x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
|
||
(x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
|
||
(x.Pass == null ? false : x.Pass.Contains(keyword))
|
||
// (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
|
||
)
|
||
.Select(x => new
|
||
{
|
||
Id = x.Id,
|
||
PrefixName = x.PrefixName,
|
||
FirstName = x.FirstName,
|
||
LastName = x.LastName,
|
||
ProfileImg = x.ProfileImg,
|
||
CitizenId = x.CitizenId,
|
||
Number = x.Number,
|
||
RegisterDate = x.RegisterDate,
|
||
ExamIdenNumber = x.ExamIdenNumber,
|
||
SeatNumber = x.SeatNumber,
|
||
ResultA = x.ResultA,
|
||
ResultB = x.ResultB,
|
||
ResultC = x.ResultC,
|
||
Pass = x.Pass,
|
||
Email = x.Email,
|
||
Status = x.Status,
|
||
PositionLevelName = x.PositionExam == null ? null : x.PositionExam.PositionLevelName,
|
||
PositionName = x.PositionExam == null ? null : x.PositionExam.PositionName,
|
||
IsShowExamInfo = x.IsShowExamInfo,
|
||
})
|
||
.Skip((page - 1) * pageSize)
|
||
.Take(pageSize)
|
||
.ToListAsync();
|
||
var _candidate = await _context.Candidates.AsQueryable()
|
||
.Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
|
||
.Where(x =>
|
||
//(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
|
||
//(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
|
||
(x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
|
||
(x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
|
||
//(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
|
||
//(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
|
||
//(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
|
||
(x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
|
||
(x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
|
||
(x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
|
||
(x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
|
||
(x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
|
||
(x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
|
||
(x.Pass == null ? false : x.Pass.Contains(keyword))
|
||
// (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
|
||
)
|
||
.CountAsync();
|
||
if (candidate.Where(x => x.Status == "done").FirstOrDefault() != null)
|
||
candidate = candidate.OrderBy(x => Convert.ToInt32(x.Number)).ToList();
|
||
var i = 0;
|
||
foreach (var item in candidate)
|
||
{
|
||
if (candidate[i].ProfileImg != null)
|
||
candidate[i].ProfileImg.Detail = _minioService.ImagesPath(candidate[i].ProfileImg.Id).Result;
|
||
i++;
|
||
}
|
||
return new { data = candidate, total = _candidate };
|
||
}
|
||
else
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
.Include(x => x.ProfileImg)
|
||
.OrderByDescending(d => d.CreatedAt)
|
||
.Where(x => x.PeriodExam == periodExam && x.Status == status)
|
||
.Where(x =>
|
||
//(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
|
||
//(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
|
||
(x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
|
||
(x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
|
||
//(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
|
||
//(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
|
||
//(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
|
||
(x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
|
||
(x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
|
||
(x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
|
||
(x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
|
||
(x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
|
||
(x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
|
||
(x.Pass == null ? false : x.Pass.Contains(keyword))
|
||
// (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
|
||
)
|
||
.Skip((page - 1) * pageSize)
|
||
.Take(pageSize)
|
||
.Select(x => new
|
||
{
|
||
Id = x.Id,
|
||
PrefixName = x.PrefixName,
|
||
FirstName = x.FirstName,
|
||
LastName = x.LastName,
|
||
ProfileImg = x.ProfileImg,
|
||
CitizenId = x.CitizenId,
|
||
Number = x.Number,
|
||
RegisterDate = x.RegisterDate,
|
||
ExamIdenNumber = x.ExamIdenNumber,
|
||
SeatNumber = x.SeatNumber,
|
||
ResultA = x.ResultA,
|
||
ResultB = x.ResultB,
|
||
ResultC = x.ResultC,
|
||
Pass = x.Pass,
|
||
Email = x.Email,
|
||
Status = x.Status,
|
||
PositionLevelName = x.PositionExam == null ? null : x.PositionExam.PositionLevelName,
|
||
PositionName = x.PositionExam == null ? null : x.PositionExam.PositionName,
|
||
IsShowExamInfo = x.IsShowExamInfo,
|
||
})
|
||
.ToListAsync();
|
||
var _candidate = await _context.Candidates.AsQueryable()
|
||
.Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
|
||
.Where(x =>
|
||
//(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
|
||
//(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
|
||
(x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
|
||
(x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
|
||
//(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
|
||
//(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
|
||
//(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
|
||
(x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
|
||
(x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
|
||
(x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
|
||
(x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
|
||
(x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
|
||
(x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
|
||
(x.Pass == null ? false : x.Pass.Contains(keyword))
|
||
// (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
|
||
)
|
||
.CountAsync();
|
||
if (candidate.Where(x => x.Status == "done").FirstOrDefault() != null)
|
||
candidate = candidate.OrderBy(x => Convert.ToInt32(x.Number)).ToList();
|
||
var i = 0;
|
||
foreach (var item in candidate)
|
||
{
|
||
if (candidate[i].ProfileImg != null)
|
||
candidate[i].ProfileImg.Detail = _minioService.ImagesPath(candidate[i].ProfileImg.Id).Result;
|
||
i++;
|
||
}
|
||
return new { data = candidate, total = _candidate };
|
||
}
|
||
}
|
||
|
||
public async Task<CandidateInformationResponseItem?> GetsAsyncInformation(string candidateId)
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.Select(x => new CandidateInformationResponseItem
|
||
{
|
||
Prefix = x.PrefixName,
|
||
PrefixId = x.PrefixId != null ? x.PrefixId.ToString() : null,
|
||
FirstName = x.FirstName,
|
||
LastName = x.LastName,
|
||
Nationality = x.Nationality,
|
||
DateOfBirth = x.DateOfBirth,
|
||
Religion = x.ReligionName,
|
||
ReligionId = x.ReligionId != null ? x.ReligionId.ToString() : null,
|
||
CitizenProvince = x.CitizenProvinceName,
|
||
CitizenProvinceId = x.CitizenProvinceId != null ? x.CitizenProvinceId.ToString() : null,
|
||
CitizenDistrict = x.CitizenDistrictName,
|
||
CitizenDistrictId = x.CitizenDistrictId != null ? x.CitizenDistrictId.ToString() : null,
|
||
CitizenDate = x.CitizenDate,
|
||
Email = x.Email,
|
||
CitizenId = x.CitizenId,
|
||
Telephone = x.Telephone,
|
||
MobilePhone = x.MobilePhone,
|
||
Knowledge = x.Knowledge,
|
||
ProfileImg = x.ProfileImg == null ? "" : x.ProfileImg.Id.ToString(),
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
|
||
if (candidate == null)
|
||
return candidate;
|
||
|
||
if (candidate.ProfileImg != null && candidate.ProfileImg != "")
|
||
candidate.ProfileImg = _minioService.ImagesPath(Guid.Parse(candidate.ProfileImg)).Result;
|
||
|
||
return candidate;
|
||
}
|
||
|
||
public async Task<string> GetsAsyncProfileImage(string candidateId)
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.Include(x => x.ProfileImg)
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.FirstOrDefaultAsync();
|
||
|
||
if (candidate == null)
|
||
throw new Exception(GlobalMessages.CandidateNotFound);
|
||
|
||
if (candidate.ProfileImg != null)
|
||
candidate.ProfileImg.Detail = _minioService.ImagesPath(candidate.ProfileImg.Id).Result;
|
||
|
||
return candidate.ProfileImg == null ? "" : candidate.ProfileImg.Detail;
|
||
}
|
||
|
||
public async Task<CandidateOccupationResponseItem?> GetsAsyncOccupation(string candidateId)
|
||
{
|
||
return await _context.Candidates.AsQueryable()
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.Select(x => new CandidateOccupationResponseItem
|
||
{
|
||
OccupationOrg = x.OccupationOrg,
|
||
OccupationPile = x.OccupationPile,
|
||
OccupationGroup = x.OccupationGroup,
|
||
OccupationSalary = x.OccupationSalary,
|
||
OccupationPosition = x.OccupationPosition,
|
||
OccupationPositionType = x.OccupationPositionType,
|
||
OccupationTelephone = x.OccupationTelephone,
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
}
|
||
|
||
public async Task<CandidateContactResponseItem?> GetsAsyncContact(string candidateId)
|
||
{
|
||
return await _context.Candidates.AsQueryable()
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.Select(x => new CandidateContactResponseItem
|
||
{
|
||
ContactPrefixId = x.ContactPrefixId,
|
||
ContactPrefix = x.ContactPrefixName,
|
||
ContactFirstname = x.ContactFirstname,
|
||
ContactLastname = x.ContactLastname,
|
||
ContactRelations = x.ContactRelations,
|
||
ContactTel = x.ContactTel,
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
}
|
||
|
||
public async Task<CandidateAddressResponseItem?> GetsAsyncAddress(string candidateId)
|
||
{
|
||
return await _context.Candidates.AsQueryable()
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.Select(x => new CandidateAddressResponseItem
|
||
{
|
||
RegistAddress = x.RegistAddress,
|
||
RegistProvince = x.RegistProvinceName,
|
||
RegistProvinceId = x.RegistProvinceId != null ? x.RegistProvinceId.ToString() : null,
|
||
RegistDistrict = x.RegistDistrictName,
|
||
RegistDistrictId = x.RegistDistrictId != null ? x.RegistDistrictId.ToString() : null,
|
||
RegistSubDistrict = x.RegistSubDistrictName,
|
||
RegistSubDistrictId = x.RegistSubDistrictId != null ? x.RegistSubDistrictId.ToString() : null,
|
||
RegistZipCode = x.RegistZipCode,
|
||
RegistSame = x.RegistSame,
|
||
CurrentAddress = x.CurrentAddress,
|
||
CurrentProvince = x.CurrentProvinceName,
|
||
CurrentProvinceId = x.CurrentProvinceId != null ? x.CurrentProvinceId.ToString() : null,
|
||
CurrentDistrict = x.CurrentDistrictName,
|
||
CurrentDistrictId = x.CurrentDistrictId != null ? x.CurrentDistrictId.ToString() : null,
|
||
CurrentSubDistrict = x.CurrentSubDistrictName,
|
||
CurrentSubDistrictId = x.CurrentSubDistrictId != null ? x.CurrentSubDistrictId.ToString() : null,
|
||
CurrentZipCode = x.CurrentZipCode,
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
}
|
||
|
||
public async Task<CandidateFamilyResponseItem?> GetsAsyncFamily(string candidateId)
|
||
{
|
||
return await _context.Candidates.AsQueryable()
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.Select(x => new CandidateFamilyResponseItem
|
||
{
|
||
Marry = x.Marry,
|
||
MarryPrefix = x.MarryPrefixName,
|
||
MarryPrefixId = x.MarryPrefixId != null ? x.MarryPrefixId.ToString() : null,
|
||
MarryFirstName = x.MarryFirstName,
|
||
MarryLastName = x.MarryLastName,
|
||
MarryOccupation = x.MarryOccupation,
|
||
MarryNationality = x.MarryNationality,
|
||
FatherPrefix = x.FatherPrefixName,
|
||
FatherPrefixId = x.FatherPrefixId != null ? x.FatherPrefixId.ToString() : null,
|
||
FatherFirstName = x.FatherFirstName,
|
||
FatherLastName = x.FatherLastName,
|
||
FatherOccupation = x.FatherOccupation,
|
||
FatherNationality = x.FatherNationality,
|
||
MotherPrefix = x.MotherPrefixName,
|
||
MotherPrefixId = x.MotherPrefixId != null ? x.MotherPrefixId.ToString() : null,
|
||
MotherFirstName = x.MotherFirstName,
|
||
MotherLastName = x.MotherLastName,
|
||
MotherOccupation = x.MotherOccupation,
|
||
MotherNationality = x.MotherNationality,
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
}
|
||
|
||
public async Task<Education?> GetsAsyncEducation(string candidateId)
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(candidateId));
|
||
|
||
if (candidate == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
return await _context.Educations.AsQueryable()
|
||
.Where(x => x.Candidate == candidate)
|
||
.FirstOrDefaultAsync();
|
||
}
|
||
|
||
public async Task<IEnumerable<Career?>> GetsAsyncCareer(string candidateId)
|
||
{
|
||
return await _context.Careers.AsQueryable()
|
||
.Where(x => x.Candidate.Id == Guid.Parse(candidateId))
|
||
.OrderBy(d => d.DurationStart)
|
||
.ToListAsync();
|
||
}
|
||
|
||
public async Task<IEnumerable<FileListResponse?>> GetsAsyncDocument(string candidateId)
|
||
{
|
||
var candidate = await _context.Candidates.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(candidateId));
|
||
|
||
if (candidate == null)
|
||
throw new Exception(GlobalMessages.CandidateNotFound);
|
||
|
||
var document = await _context.CandidateDocuments.AsQueryable()
|
||
.Where(x => x.Candidate == candidate)
|
||
.OrderBy(d => d.CreatedAt)
|
||
.Select(x => new FileListResponse
|
||
{
|
||
Id = x.Document == null ? "" : x.Document.Id.ToString(),
|
||
FileName = x.Document == null ? "" : x.Document.FileName,
|
||
FileType = x.Document == null ? "" : x.Document.FileType,
|
||
FileSize = x.Document == null ? 0 : x.Document.FileSize,
|
||
Detail = x.Document == null ? "" : x.Document.Id.ToString(),
|
||
})
|
||
.ToListAsync();
|
||
|
||
var i = 0;
|
||
foreach (var item in document)
|
||
{
|
||
if (document[i].Detail != null && document[i].Detail != "")
|
||
document[i].Detail = _minioService.ImagesPath(Guid.Parse(document[i].Detail)).Result;
|
||
i++;
|
||
}
|
||
|
||
return document;
|
||
}
|
||
|
||
private async Task<List<RequestImportSeat>> ReadExcelCandidate(IFormFile formFile)
|
||
{
|
||
var list = new List<RequestImportSeat>();
|
||
using (var stream = new MemoryStream())
|
||
{
|
||
await formFile.CopyToAsync(stream);
|
||
using (var package = new ExcelPackage(stream))
|
||
{
|
||
for (int i = 0; i < package.Workbook.Worksheets.Count(); i++)
|
||
{
|
||
ExcelWorksheet worksheet = package.Workbook.Worksheets[i];
|
||
var rowCount = worksheet.Dimension.Rows;
|
||
for (int row = 4; row <= rowCount; row++)
|
||
{
|
||
System.Diagnostics.Debug.WriteLine(worksheet);
|
||
// if (worksheet.Cells[row, 2].Value != null)
|
||
// {
|
||
list.Add(new RequestImportSeat
|
||
{
|
||
Number = worksheet.Cells[row, 1].Value != null ? worksheet.Cells[row, 1].Value.ToString() : null,
|
||
ExamIdenNumber = worksheet.Cells[row, 2].Value != null ? worksheet.Cells[row, 2].Value.ToString() : null,
|
||
CitizenId = worksheet.Cells[row, 3].Value != null ? worksheet.Cells[row, 3].Value.ToString() : null,
|
||
SeatNumber = worksheet.Cells[row, 4].Value != null ? worksheet.Cells[row, 4].Value.ToString() : null,
|
||
|
||
PointPath1A = worksheet.Cells[row, 9].Value != null ? worksheet.Cells[row, 9].Value.ToString() : null,
|
||
PointPath2A = worksheet.Cells[row, 10].Value != null ? worksheet.Cells[row, 10].Value.ToString() : null,
|
||
PointPath3A = worksheet.Cells[row, 11].Value != null ? worksheet.Cells[row, 11].Value.ToString() : null,
|
||
PointTotalA = worksheet.Cells[3, 12].Value != null ? worksheet.Cells[3, 12].Value.ToString()?.Split(" ")[0] : null,
|
||
PointA = worksheet.Cells[row, 12].Value != null ? worksheet.Cells[row, 12].Value.ToString() : null,
|
||
PointPerA = worksheet.Cells[row, 13].Value != null ? worksheet.Cells[row, 13].Value.ToString() : null,
|
||
ResultA = worksheet.Cells[row, 14].Value != null ? worksheet.Cells[row, 14].Value.ToString() : null,
|
||
|
||
PointTotalB = worksheet.Cells[3, 15].Value != null ? worksheet.Cells[3, 15].Value.ToString()?.Split(" ")[0] : null,
|
||
PointB = worksheet.Cells[row, 15].Value != null ? worksheet.Cells[row, 15].Value.ToString() : null,
|
||
PointPerB = worksheet.Cells[row, 16].Value != null ? worksheet.Cells[row, 16].Value.ToString() : null,
|
||
ResultB = worksheet.Cells[row, 17].Value != null ? worksheet.Cells[row, 17].Value.ToString() : null,
|
||
|
||
PointPath1C = worksheet.Cells[row, 18].Value != null ? worksheet.Cells[row, 18].Value.ToString() : null,
|
||
PointPath2C = worksheet.Cells[row, 19].Value != null ? worksheet.Cells[row, 19].Value.ToString() : null,
|
||
PointTotalC = worksheet.Cells[3, 20].Value != null ? worksheet.Cells[3, 20].Value.ToString()?.Split(" ")[0] : null,
|
||
PointC = worksheet.Cells[row, 20].Value != null ? worksheet.Cells[row, 20].Value.ToString() : null,
|
||
PointPerC = worksheet.Cells[row, 21].Value != null ? worksheet.Cells[row, 21].Value.ToString() : null,
|
||
ResultC = worksheet.Cells[row, 22].Value != null ? worksheet.Cells[row, 22].Value.ToString() : null,
|
||
|
||
Pass = worksheet.Cells[row, 23].Value != null ? worksheet.Cells[row, 23].Value.ToString() : null,
|
||
ExamReason = worksheet.Cells[row, 27].Value != null ? worksheet.Cells[row, 27].Value.ToString() : null,
|
||
});
|
||
if (!int.TryParse(worksheet.Cells[row, 9].Value != null ? worksheet.Cells[row, 9].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 10].Value != null ? worksheet.Cells[row, 10].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 11].Value != null ? worksheet.Cells[row, 11].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[3, 12].Value != null ? worksheet.Cells[3, 12].Value.ToString()?.Split(" ")[0] : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 12].Value != null ? worksheet.Cells[row, 12].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 13].Value != null ? worksheet.Cells[row, 13].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[3, 15].Value != null ? worksheet.Cells[3, 15].Value.ToString()?.Split(" ")[0] : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 15].Value != null ? worksheet.Cells[row, 15].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 16].Value != null ? worksheet.Cells[row, 16].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 18].Value != null ? worksheet.Cells[row, 18].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 19].Value != null ? worksheet.Cells[row, 19].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[3, 20].Value != null ? worksheet.Cells[3, 20].Value.ToString()?.Split(" ")[0] : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 20].Value != null ? worksheet.Cells[row, 20].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
if (!int.TryParse(worksheet.Cells[row, 21].Value != null ? worksheet.Cells[row, 21].Value.ToString() : "0", out _))
|
||
throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
|
||
// }
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return list;
|
||
}
|
||
|
||
public async Task UploadSeatCandidateAsync(string examId, IFormFile excels)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.ToListAsync();
|
||
|
||
var items = await ReadExcelCandidate(excels);
|
||
|
||
foreach (var candidate in candidates)
|
||
{
|
||
if (candidate.ExamIdenNumber == null || candidate.CitizenId == null)
|
||
continue;
|
||
var item = items.FirstOrDefault(x => x.CitizenId == candidate.CitizenId && x.ExamIdenNumber == candidate.ExamIdenNumber);
|
||
|
||
if (item != null)
|
||
{
|
||
if (candidate.Status == "checkSeat")
|
||
{
|
||
candidate.SeatNumber = item.SeatNumber;
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: ติดตามประกาศทาง Website";
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
candidate.Status = "checkPoint";
|
||
}
|
||
else
|
||
{
|
||
if (candidate.Status != "waiver")
|
||
{
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
}
|
||
candidate.Status = "waiver";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (candidate.Status != "waiver")
|
||
{
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
}
|
||
candidate.Status = "waiver";
|
||
}
|
||
periodExam.SetSeat = true;
|
||
}
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UploadPointCandidateAsync(string examId, IFormFile excels)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.ToListAsync();
|
||
|
||
var items = await ReadExcelCandidate(excels);
|
||
|
||
foreach (var candidate in candidates)
|
||
{
|
||
if (candidate.ExamIdenNumber == null || candidate.CitizenId == null)
|
||
continue;
|
||
var item = items.FirstOrDefault(x => x.CitizenId == candidate.CitizenId && x.ExamIdenNumber == candidate.ExamIdenNumber);
|
||
|
||
if (item != null)
|
||
{
|
||
if (candidate.Status == "checkPoint" || candidate.Status == "done")
|
||
{
|
||
candidate.PointTotalA = item.PointTotalA;
|
||
candidate.PointPath1A = item.PointPath1A;
|
||
candidate.PointPath2A = item.PointPath2A;
|
||
candidate.PointPath3A = item.PointPath3A;
|
||
candidate.PointA = item.PointA;
|
||
candidate.PointPerA = item.PointPerA;
|
||
candidate.ResultA = item.ResultA;
|
||
candidate.PointTotalB = item.PointTotalB;
|
||
candidate.PointB = item.PointB;
|
||
candidate.PointPerB = item.PointPerB;
|
||
candidate.ResultB = item.ResultB;
|
||
candidate.PointTotalC = item.PointTotalC;
|
||
candidate.PointPath1C = item.PointPath1C;
|
||
candidate.PointPath2C = item.PointPath2C;
|
||
candidate.PointC = item.PointC;
|
||
candidate.PointPerC = item.PointPerC;
|
||
candidate.ResultC = item.ResultC;
|
||
candidate.Pass = item.Pass;
|
||
candidate.ExamReason = item.ExamReason;
|
||
candidate.Number = item.Number;
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สอบคัดเลือกสำเร็จ <br />" + "ผลการสอบของท่าน คือ " + candidate.Pass;
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
candidate.Status = "done";
|
||
}
|
||
else
|
||
{
|
||
if (candidate.Status != "waiver")
|
||
{
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
}
|
||
// candidate.Status = "waiver";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (candidate.Status != "waiver")
|
||
{
|
||
var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
|
||
var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
|
||
if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
|
||
}
|
||
// candidate.Status = "waiver";
|
||
}
|
||
periodExam.SetSeat = true;
|
||
}
|
||
await _context.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task<MemoryStream> DownloadCandidateAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = new List<Candidate>();
|
||
if (periodExam.SetSeat == true)
|
||
{
|
||
candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
// .OrderBy(x => x.PositionExam == null ? null : x.PositionExam.PositionName)
|
||
// .ThenBy(x => x.PositionExam == null ? null : x.PositionExam.PositionLevelName)
|
||
// .ThenBy(x => x.ExamIdenNumber)
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "waiver")
|
||
.Where(x => x.Status != "register")
|
||
.ToListAsync();
|
||
}
|
||
else
|
||
{
|
||
candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
// .OrderBy(x => x.PositionExam == null ? null : x.PositionExam.PositionName)
|
||
// .ThenBy(x => x.PositionExam == null ? null : x.PositionExam.PositionLevelName)
|
||
// .ThenBy(x => x.ExamIdenNumber)
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.ToListAsync();
|
||
}
|
||
|
||
var stream = new MemoryStream();
|
||
using (var package = new ExcelPackage(stream))
|
||
{
|
||
var summarySheet = package.Workbook.Worksheets.Add("Candidate");
|
||
summarySheet.TabColor = System.Drawing.Color.Black;
|
||
// summarySheet.DefaultRowHeight = 17;
|
||
summarySheet.Row(1).Style.Font.Bold = true;
|
||
summarySheet.Cells[1, 1, 1, 8].Value = "ข้อมูลผู้สมัครสอบ";
|
||
summarySheet.Cells[1, 1, 1, 8].Merge = true;
|
||
summarySheet.Cells[2, 1, 3, 1].Value = "ลำดับที่สอบได้";
|
||
summarySheet.Cells[2, 1, 3, 1].Merge = true;
|
||
summarySheet.Cells[2, 2, 3, 2].Value = "เลขประจำตัวสอบ";
|
||
summarySheet.Cells[2, 2, 3, 2].Merge = true;
|
||
summarySheet.Cells[2, 3, 3, 3].Value = "เลขบัตรประชาชน";
|
||
summarySheet.Cells[2, 3, 3, 3].Merge = true;
|
||
summarySheet.Cells[2, 4, 3, 4].Value = "สนามสอบ";
|
||
summarySheet.Cells[2, 4, 3, 4].Merge = true;
|
||
summarySheet.Cells[2, 5, 3, 5].Value = "คำนำหน้า";
|
||
summarySheet.Cells[2, 5, 3, 5].Merge = true;
|
||
summarySheet.Cells[2, 6, 3, 6].Value = "ชื่อ-ชื่อสกุล";
|
||
summarySheet.Cells[2, 6, 3, 6].Merge = true;
|
||
summarySheet.Cells[2, 7, 3, 7].Value = "ตำแหน่ง";
|
||
summarySheet.Cells[2, 7, 3, 7].Merge = true;
|
||
summarySheet.Cells[2, 8, 3, 8].Value = "ระดับ";
|
||
summarySheet.Cells[2, 8, 3, 8].Merge = true;
|
||
|
||
summarySheet.Cells[1, 9, 1, 14].Value = "ความรู้ความสามารถทั่วไป (ภาค ก.)";
|
||
summarySheet.Cells[1, 9, 1, 14].Merge = true;
|
||
summarySheet.Cells[2, 9].Value = "ความสามารถในการคิดวิเคราะห์";
|
||
summarySheet.Cells[3, 9].Value = "100 คะแนน";
|
||
summarySheet.Cells[2, 10].Value = "ภาษาอังกฤษ";
|
||
summarySheet.Cells[3, 10].Value = "50 คะแนน";
|
||
summarySheet.Cells[2, 11].Value = "ความรู้และลักษณะการเป็นข้าราชการที่ดี";
|
||
summarySheet.Cells[3, 11].Value = "50 คะแนน";
|
||
// summarySheet.Cells[2, 9].Value = "คะแนนเต็มภาค ก";
|
||
// summarySheet.Cells[3, 9].Value = "100 คะแนน";
|
||
summarySheet.Cells[2, 12].Value = "คะแนนรวม";
|
||
summarySheet.Cells[3, 12].Value = "200 คะแนน";
|
||
summarySheet.Cells[2, 13, 3, 13].Value = "ร้อยละ";
|
||
summarySheet.Cells[2, 13, 3, 13].Merge = true;
|
||
summarySheet.Cells[2, 14, 3, 14].Value = "ผลประเมิน ภาค ก";
|
||
summarySheet.Cells[2, 14, 3, 14].Merge = true;
|
||
|
||
summarySheet.Cells[1, 15, 1, 17].Value = "ภาคความรู้ความสามารถที่ใช้เฉพาะตำแหน่ง(ภาค ข)";
|
||
summarySheet.Cells[1, 15, 1, 17].Merge = true;
|
||
// summarySheet.Cells[1, 15].Value = "คะแนนเต็มภาค ข";
|
||
summarySheet.Cells[2, 15].Value = "คะแนนรวม";
|
||
summarySheet.Cells[3, 15].Value = "200 คะแนน";
|
||
summarySheet.Cells[2, 16, 3, 16].Value = "ร้อยละ";
|
||
summarySheet.Cells[2, 16, 3, 16].Merge = true;
|
||
summarySheet.Cells[2, 17, 3, 17].Value = "ผลประเมิน ภาค ข";
|
||
summarySheet.Cells[2, 17, 3, 17].Merge = true;
|
||
|
||
summarySheet.Cells[1, 18, 1, 22].Value = "ภาคความเหมาะสมกับตำแหน่ง (ภาค ค)";
|
||
summarySheet.Cells[1, 18, 1, 22].Merge = true;
|
||
summarySheet.Cells[2, 18].Value = "ทดสอบสมรรถนะหลัก";
|
||
summarySheet.Cells[3, 18].Value = "50 คะแนน";
|
||
summarySheet.Cells[2, 19].Value = "สัมภาษณ์";
|
||
summarySheet.Cells[3, 19].Value = "50 คะแนน";
|
||
// summarySheet.Cells[1, 19].Value = "คะแนนเต็มภาค ค";
|
||
summarySheet.Cells[2, 20].Value = "คะแนนรวม";
|
||
summarySheet.Cells[3, 20].Value = "100 คะแนน";
|
||
summarySheet.Cells[2, 21, 3, 21].Value = "ร้อยละ";
|
||
summarySheet.Cells[2, 21, 3, 21].Merge = true;
|
||
summarySheet.Cells[2, 22, 3, 22].Value = "ผลประเมิน ภาค ค";
|
||
summarySheet.Cells[2, 22, 3, 22].Merge = true;
|
||
|
||
summarySheet.Cells[1, 23, 3, 23].Value = "ได้ / ตก";
|
||
summarySheet.Cells[1, 23, 3, 23].Merge = true;
|
||
summarySheet.Cells[1, 24, 3, 24].Value = "ชำระค่าธรรมเนียม";
|
||
summarySheet.Cells[1, 24, 3, 24].Merge = true;
|
||
summarySheet.Cells[1, 25, 3, 25].Value = "วันเวลาชำระ";
|
||
summarySheet.Cells[1, 25, 3, 25].Merge = true;
|
||
summarySheet.Cells[1, 26, 3, 26].Value = "วันและเวลาที่สมัคร";
|
||
summarySheet.Cells[1, 26, 3, 26].Merge = true;
|
||
summarySheet.Cells[1, 27, 3, 27].Value = "หมายเหตุ";
|
||
summarySheet.Cells[1, 27, 3, 27].Merge = true;
|
||
int row = 4;
|
||
|
||
foreach (var item in candidates)
|
||
{
|
||
summarySheet.Cells[row, 1].Value = item.Number;
|
||
summarySheet.Cells[row, 2].Value = item.ExamIdenNumber;
|
||
summarySheet.Cells[row, 3].Value = item.CitizenId;
|
||
summarySheet.Cells[row, 4].Value = item.SeatNumber;
|
||
summarySheet.Cells[row, 5].Value = item.PrefixName;
|
||
summarySheet.Cells[row, 6].Value = $"{item.FirstName} {item.LastName}";
|
||
summarySheet.Cells[row, 7].Value = item.PositionExam == null ? "-" : item.PositionExam.PositionName;
|
||
summarySheet.Cells[row, 8].Value = item.PositionExam == null ? "-" : item.PositionExam.PositionLevelName;
|
||
|
||
summarySheet.Cells[row, 9].Value = item.PointPath1A;
|
||
summarySheet.Cells[row, 10].Value = item.PointPath2A;
|
||
summarySheet.Cells[row, 11].Value = item.PointPath3A;
|
||
// summarySheet.Cells[row, 9].Value = item.PointTotalA;
|
||
summarySheet.Cells[row, 12].Value = item.PointA;
|
||
summarySheet.Cells[row, 13].Value = item.PointPerA;
|
||
summarySheet.Cells[row, 14].Value = item.ResultA;
|
||
|
||
// summarySheet.Cells[row, 15].Value = item.PointTotalB;
|
||
summarySheet.Cells[row, 15].Value = item.PointB;
|
||
summarySheet.Cells[row, 16].Value = item.PointPerB;
|
||
summarySheet.Cells[row, 17].Value = item.ResultB;
|
||
|
||
summarySheet.Cells[row, 17].Value = item.PointPath1C;
|
||
summarySheet.Cells[row, 19].Value = item.PointPath2C;
|
||
// summarySheet.Cells[row, 20].Value = item.PointTotalC;
|
||
summarySheet.Cells[row, 20].Value = item.PointC;
|
||
summarySheet.Cells[row, 21].Value = item.PointPerC;
|
||
summarySheet.Cells[row, 22].Value = item.ResultC;
|
||
|
||
summarySheet.Cells[row, 23].Value = item.Pass;
|
||
summarySheet.Cells[row, 24].Value = item.PeriodExam == null || item.PeriodExam.Fee <= 0 ? "ไม่มีชำระเงิน" : (item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว");
|
||
summarySheet.Cells[row, 25].Value = item.PaymentDate == null ? "-" : $"{item.PaymentDate.Value.ToThaiShortDate()} {item.PaymentDate.Value.ToString("HH:mm:ss.ffff")}";//xxxxxxxxxxxx
|
||
summarySheet.Cells[row, 26].Value = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}";
|
||
summarySheet.Cells[row, 27].Value = item.ExamReason;
|
||
row++;
|
||
}
|
||
summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
|
||
package.Save();
|
||
}
|
||
|
||
stream.Position = 0;
|
||
return stream;
|
||
}
|
||
|
||
public async Task<dynamic> NewDownloadCandidateAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = new List<Candidate>();
|
||
if (periodExam.SetSeat == true)
|
||
{
|
||
candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "waiver")
|
||
.Where(x => x.Status != "register")
|
||
.ToListAsync();
|
||
}
|
||
else
|
||
{
|
||
candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Include(x => x.PositionExam)
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.ToListAsync();
|
||
}
|
||
var _data = new List<object>();
|
||
|
||
if (candidates.Count > 0)
|
||
{
|
||
foreach (var item in candidates)
|
||
{
|
||
_data.Add(new
|
||
{
|
||
Number = item.Number != null ? item.Number.ToString() : "-",
|
||
ExamIdenNumber = item.ExamIdenNumber != null ? item.ExamIdenNumber.ToString() : "-",
|
||
CitizenId = item.CitizenId != null ? item.CitizenId.ToString() : "-",
|
||
SeatNumber = item.SeatNumber != null ? item.SeatNumber.ToString() : "-",
|
||
PrefixName = item.PrefixName != null ? item.PrefixName.ToString() : "-",
|
||
FullName = item.FirstName != null && item.LastName != null ? $"{item.FirstName} {item.LastName}" : "-",
|
||
PositionName = item.PositionExam != null && item.PositionExam?.PositionName != null ? item.PositionExam?.PositionName.ToString() : "-",
|
||
PositionLevelName = item.PositionExam != null && item.PositionExam?.PositionLevelName != null ? item.PositionExam?.PositionLevelName.ToString() : "-",
|
||
|
||
PointPath1A = item.PointPath1A != null ? item.PointPath1A.ToString() : "0",
|
||
PointPath2A = item.PointPath2A != null ? item.PointPath2A.ToString() : "0",
|
||
PointPath3A = item.PointPath3A != null ? item.PointPath3A.ToString() : "0",
|
||
PointA = item.PointA != null ? item.PointA.ToString() : "0",
|
||
PointPerA = item.PointPerA != null ? item.PointPerA.ToString() : "0",
|
||
ResultA = item.ResultA != null ? item.ResultA.ToString() : "-",
|
||
|
||
PointB = item.PointB != null ? item.PointB.ToString() : "0",
|
||
PointPerB = item.PointPerB != null ? item.PointPerB.ToString() : "0",
|
||
ResultB = item.ResultB != null ? item.ResultB.ToString() : "-",
|
||
|
||
PointPath1C = item.PointPath1C != null ? item.PointPath1C.ToString() : "0",
|
||
PointPath2C = item.PointPath2C != null ? item.PointPath2C.ToString() : "0",
|
||
PointC = item.PointC != null ? item.PointC.ToString() : "0",
|
||
PointPerC = item.PointPerC != null ? item.PointPerC.ToString() : "0",
|
||
ResultC = item.ResultC != null ? item.ResultC.ToString() : "-",
|
||
|
||
Pass = item.Pass != null ? item.Pass.ToString() : "-",
|
||
PaymentStatus = item.PeriodExam == null || item.PeriodExam.Fee <= 0 ? "ไม่มีชำระเงิน" : (item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว"),
|
||
PaymentDate = item.PaymentDate == null ? "-" : $"{item.PaymentDate.Value.ToThaiShortDate()} {item.PaymentDate.Value:HH:mm:ss.ffff}".ToString(),
|
||
RegisterDate = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value:HH:mm:ss.ffff}".ToString(),
|
||
ExamReason = item.ExamReason != null ? item.ExamReason.ToString() : "-"
|
||
});
|
||
}
|
||
}
|
||
else
|
||
{
|
||
_data.Add(new
|
||
{
|
||
Number = "-",
|
||
ExamIdenNumber = "-",
|
||
CitizenId = "-",
|
||
SeatNumber = "-",
|
||
PrefixName = "-",
|
||
FullName = "-",
|
||
PositionName = "-",
|
||
PositionLevelName = "-",
|
||
|
||
PointPath1A = "-",
|
||
PointPath2A = "-",
|
||
PointPath3A = "-",
|
||
PointA = "-",
|
||
PointPerA = "-",
|
||
ResultA = "-",
|
||
|
||
PointB = "-",
|
||
PointPerB = "-",
|
||
ResultB = "-",
|
||
|
||
PointPath1C = "-",
|
||
PointPath2C = "-",
|
||
PointC = "-",
|
||
PointPerC = "-",
|
||
ResultC = "-",
|
||
|
||
Pass = "-",
|
||
PaymentStatus = "-",
|
||
PaymentDate = "-",
|
||
RegisterDate = "-",
|
||
ExamReason = "-"
|
||
});
|
||
}
|
||
|
||
return new
|
||
{
|
||
template = "candidate",
|
||
reportName = $"Candidate_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
|
||
data = new
|
||
{
|
||
data = _data
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<PeriodExam> GetsPaymentExamAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.Include(x => x.BankExam)
|
||
.Where(x => x.Id == Guid.Parse(examId))
|
||
.Select(x => new PeriodExam
|
||
{
|
||
BankExam = x.BankExam,
|
||
Fee = x.Fee,
|
||
})
|
||
.FirstOrDefaultAsync();
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
return periodExam;
|
||
}
|
||
|
||
public async Task<dynamic> ExportsPaymentExamAsync(string candidateId)
|
||
{
|
||
var periodExam = await _context.Candidates.AsQueryable()
|
||
.Include(x => x.PeriodExam)
|
||
.ThenInclude(x => x.PeriodExamBarCodes)
|
||
.ThenInclude(x => x.Document)
|
||
.Include(x => x.PeriodExam)
|
||
.ThenInclude(x => x.PeriodExamQrCodes)
|
||
.ThenInclude(x => x.Document)
|
||
.Where(x => x.Id == Guid.Parse(candidateId))
|
||
.FirstOrDefaultAsync();
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
return new
|
||
{
|
||
template = "candidate-payment",
|
||
reportName = $"Candidate_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
|
||
data = new
|
||
{
|
||
Remark = periodExam?.PeriodExam?.Remark ?? "-",
|
||
CompanyCode = periodExam?.PeriodExam?.CompanyCode ?? "-",
|
||
PeriodExamBarCodes = (periodExam?.PeriodExam?.PeriodExamBarCodes[0]?.Document?.Id ?? null) == null ? "" : _minioService.ImagesPath(periodExam?.PeriodExam?.PeriodExamBarCodes[0]?.Document?.Id ?? Guid.Parse("00000000-0000-0000-0000-000000000000")).Result,
|
||
Reason = periodExam?.PeriodExam?.Reason ?? "-",
|
||
RefNo1 = periodExam?.PeriodExam?.RefNo1 ?? "-",
|
||
PeriodExamQrCodes = (periodExam?.PeriodExam?.PeriodExamQrCodes[0]?.Document?.Id ?? null) == null ? "" : _minioService.ImagesPath(periodExam?.PeriodExam?.PeriodExamQrCodes[0]?.Document?.Id ?? Guid.Parse("00000000-0000-0000-0000-000000000000")).Result,
|
||
CitizenId = periodExam?.CitizenId ?? "-",
|
||
FullName = $"{periodExam.PrefixName}{periodExam.FirstName} {periodExam.LastName}",
|
||
Fee = periodExam?.PeriodExam?.Fee?.ToString() ?? "-",
|
||
Date = DateTime.Now.Date.ToThaiShortDate(),
|
||
FeeText = (periodExam?.PeriodExam?.Fee ?? null) == null ? "-" : periodExam?.PeriodExam?.Fee.Value.NumberToThaiText() ?? "-",
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<List<DashboardResponseItem>> GetsDashboardPaymentExamAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.Include(x => x.Candidate)
|
||
.ThenInclude(x => x.PaymentImg)
|
||
.Where(x => x.Id == Guid.Parse(examId))
|
||
.FirstOrDefaultAsync();
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var dashboard = new List<DashboardResponseItem>
|
||
{
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 1,
|
||
Name = "จำนวนผู้สมัครคัดเลือกทั้งหมด",
|
||
Count = periodExam.Candidate.Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 2,
|
||
Name = "จำนวนผู้ยืนยันการสมัครคัดเลือก",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 3,
|
||
Name = "จำนวนผู้มีสิทธิ์เข้ารับคัดเลือกทั้งหมด",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.ExamIdenNumber != null).Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 4,
|
||
Name = "จำนวนผู้เข้ารับการคัดเลือกทั้งหมด",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.SeatNumber != null).Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 5,
|
||
Name = "ผ่านการสอบ",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.Pass=="ผ่าน" || x.Pass=="ได้").Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 6,
|
||
Name = "ไม่ผ่านการสอบ",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.Pass!=null && x.Pass!="ผ่าน" && x.Pass!="ได้").Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 7,
|
||
Name = "เพศชาย",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.PrefixName != null && x.PrefixName=="นาย").Count()
|
||
},
|
||
new DashboardResponseItem
|
||
{
|
||
Id = 8,
|
||
Name = "เพศหญิง",
|
||
Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.PrefixName != null && (x.PrefixName=="นาง" || x.PrefixName=="นางสาว")).Count()
|
||
},
|
||
// new DashboardResponseItem
|
||
// {
|
||
// Id = 8,
|
||
// Name = "เพศอื่นๆ",
|
||
// Count = periodExam.Candidate.Count() -periodExam.Candidate.Where(x=>x.PrefixName != null && x.PrefixName.Contains("ชาย")).Count()-periodExam.Candidate.Where(x=>x.PrefixName != null && x.PrefixName.Contains("หญิง")).Count()
|
||
// },
|
||
};
|
||
|
||
return dashboard;
|
||
}
|
||
|
||
public async Task<MemoryStream> DownloadCandidateAllAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.Select(c => new
|
||
{
|
||
Id = c.Id,
|
||
Status = c.Status,
|
||
CitizenId = c.CitizenId,
|
||
FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
|
||
Nationality = c.Nationality,
|
||
DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
|
||
Age = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.CalculateAgeStrV2(0, 0),
|
||
ReligionName = c.ReligionName,
|
||
Telephone = c.Telephone,
|
||
MobilePhone = c.MobilePhone,
|
||
Email = c.Email,
|
||
Knowledge = c.Knowledge,
|
||
RegistAddress = c.RegistAddress,
|
||
RegistProvinceName = c.RegistProvinceName,
|
||
RegistDistrictName = c.RegistDistrictName,
|
||
RegistSubDistrictName = c.RegistSubDistrictName,
|
||
RegistZipCode = c.RegistZipCode,
|
||
CurrentAddress = c.CurrentAddress,
|
||
CurrentProvinceName = c.CurrentProvinceName,
|
||
CurrentDistrictName = c.CurrentDistrictName,
|
||
CurrentSubDistrictName = c.CurrentSubDistrictName,
|
||
CurrentZipCode = c.CurrentZipCode,
|
||
OccupationOrg = c.OccupationOrg,
|
||
OccupationPile = c.OccupationPile,
|
||
OccupationGroup = c.OccupationGroup,
|
||
OccupationSalary = c.OccupationSalary,
|
||
OccupationPosition = c.OccupationPosition,
|
||
OccupationPositionType = c.OccupationPositionType,
|
||
OccupationTelephone = c.OccupationTelephone,
|
||
RegisterDate = c.RegisterDate,
|
||
PositionName = c.PositionExam.PositionName,
|
||
PositionLevelName = c.PositionExam.PositionLevelName,
|
||
|
||
ContactFullName = $"{c.ContactPrefixName}{c.ContactFirstname} {c.ContactLastname}",
|
||
ContactRelations = c.ContactRelations,
|
||
ContactTel = c.ContactTel,
|
||
|
||
Number = c.Number,
|
||
ExamIdenNumber = c.ExamIdenNumber,
|
||
SeatNumber = c.SeatNumber,
|
||
PointTotalA = c.PointTotalA,
|
||
PointPath1A = c.PointPath1A,
|
||
PointPath2A = c.PointPath2A,
|
||
PointPath3A = c.PointPath3A,
|
||
PointA = c.PointA,
|
||
PointPerA = c.PointPerA,
|
||
ResultA = c.ResultA,
|
||
PointTotalB = c.PointTotalB,
|
||
PointB = c.PointB,
|
||
PointPerB = c.PointPerB,
|
||
ResultB = c.ResultB,
|
||
PointTotalC = c.PointTotalC,
|
||
PointPath1C = c.PointPath1C,
|
||
PointPath2C = c.PointPath2C,
|
||
PointC = c.PointC,
|
||
PointPerC = c.PointPerC,
|
||
ResultC = c.ResultC,
|
||
Pass = c.Pass,
|
||
ExamReason = c.ExamReason,
|
||
CreatedAt = c.CreatedAt,
|
||
})
|
||
.ToListAsync();
|
||
var stream = new MemoryStream();
|
||
using (var package = new ExcelPackage(stream))
|
||
{
|
||
var summarySheet = package.Workbook.Worksheets.Add("Candidate");
|
||
summarySheet.TabColor = System.Drawing.Color.Black;
|
||
// summarySheet.DefaultRowHeight = 17;
|
||
summarySheet.Row(1).Style.Font.Bold = true;
|
||
summarySheet.Cells[1, 1, 2, 1].Value = "ลำดับ";
|
||
summarySheet.Cells[1, 1, 2, 1].Merge = true;
|
||
summarySheet.Cells[1, 2, 2, 2].Value = "ตำแหน่งที่สมัครสอบ";
|
||
summarySheet.Cells[1, 2, 2, 2].Merge = true;
|
||
summarySheet.Cells[1, 3, 2, 3].Value = "ชื่อ-สกุล";
|
||
summarySheet.Cells[1, 3, 2, 3].Merge = true;
|
||
summarySheet.Cells[1, 4, 2, 4].Value = "สัญชาติ";
|
||
summarySheet.Cells[1, 4, 2, 4].Merge = true;
|
||
summarySheet.Cells[1, 5, 2, 5].Value = "ศาสนา";
|
||
summarySheet.Cells[1, 5, 2, 5].Merge = true;
|
||
summarySheet.Cells[1, 6, 2, 6].Value = "วันเดือนปีเกิด";
|
||
summarySheet.Cells[1, 6, 2, 6].Merge = true;
|
||
summarySheet.Cells[1, 7, 2, 7].Value = "อายุ";
|
||
summarySheet.Cells[1, 7, 2, 7].Merge = true;
|
||
summarySheet.Cells[1, 8, 2, 8].Value = "โทรศัพท์";
|
||
summarySheet.Cells[1, 8, 2, 8].Merge = true;
|
||
summarySheet.Cells[1, 9, 2, 9].Value = "อีเมล";
|
||
summarySheet.Cells[1, 9, 2, 9].Merge = true;
|
||
summarySheet.Cells[1, 10, 2, 10].Value = "เลขบัตรประจำตัวประชาชน";
|
||
summarySheet.Cells[1, 10, 2, 10].Merge = true;
|
||
summarySheet.Cells[1, 11, 2, 11].Value = "วุฒิที่ใช้ในการสมัคร";
|
||
summarySheet.Cells[1, 11, 2, 11].Merge = true;
|
||
summarySheet.Cells[1, 12, 2, 12].Value = "ชื่อปริญญา";
|
||
summarySheet.Cells[1, 12, 2, 12].Merge = true;
|
||
summarySheet.Cells[1, 13, 2, 13].Value = "สาขาวิชา/วิชาเอก";
|
||
summarySheet.Cells[1, 13, 2, 13].Merge = true;
|
||
summarySheet.Cells[1, 14, 2, 14].Value = "สถานศึกษา";
|
||
summarySheet.Cells[1, 14, 2, 14].Merge = true;
|
||
summarySheet.Cells[1, 15, 2, 15].Value = "วันที่สำเร็จการศึกษา";
|
||
summarySheet.Cells[1, 15, 2, 15].Merge = true;
|
||
summarySheet.Cells[1, 16, 2, 16].Value = "คะแนนเฉลี่ยสะสม";
|
||
summarySheet.Cells[1, 16, 2, 16].Merge = true;
|
||
summarySheet.Cells[1, 17, 2, 17].Value = "ประเภทสถานศึกษา";
|
||
summarySheet.Cells[1, 17, 2, 17].Merge = true;
|
||
summarySheet.Cells[1, 18, 2, 18].Value = "วุฒิการศึกษาสูงสุด";
|
||
summarySheet.Cells[1, 18, 2, 18].Merge = true;
|
||
summarySheet.Cells[1, 19, 2, 19].Value = "ประเภทตำแหน่งปัจจุบัน";
|
||
summarySheet.Cells[1, 19, 2, 19].Merge = true;
|
||
summarySheet.Cells[1, 20, 2, 20].Value = "ชื่อตำแหน่ง";
|
||
summarySheet.Cells[1, 20, 2, 20].Merge = true;
|
||
summarySheet.Cells[1, 21, 2, 21].Value = "เงินเดือน";
|
||
summarySheet.Cells[1, 21, 2, 21].Merge = true;
|
||
summarySheet.Cells[1, 22, 2, 22].Value = "กลุ่ม/ฝ่าย";
|
||
summarySheet.Cells[1, 22, 2, 22].Merge = true;
|
||
summarySheet.Cells[1, 23, 2, 23].Value = "กอง";
|
||
summarySheet.Cells[1, 23, 2, 23].Merge = true;
|
||
summarySheet.Cells[1, 24, 2, 24].Value = "สังกัด";
|
||
summarySheet.Cells[1, 24, 2, 24].Merge = true;
|
||
summarySheet.Cells[1, 25, 2, 25].Value = "เบอร์โทรที่ทำงาน";
|
||
summarySheet.Cells[1, 25, 2, 25].Merge = true;
|
||
|
||
summarySheet.Cells[1, 26, 1, 31].Value = "ประวัติการทำงาน (ตั้งแต่เริ่มปฏิบัติงานกับกรุงเทพฯ - ปัจจุบัน)";
|
||
summarySheet.Cells[1, 26, 1, 31].Merge = true;
|
||
summarySheet.Cells[2, 26].Value = "ตำแหน่ง";
|
||
summarySheet.Cells[2, 27].Value = "ประเภท";
|
||
summarySheet.Cells[2, 28].Value = "เริ่ม";
|
||
summarySheet.Cells[2, 29].Value = "สิ้นสุด";
|
||
summarySheet.Cells[2, 30].Value = "ระยะเวลา";
|
||
summarySheet.Cells[2, 31].Value = "ระยะเวลารวม";
|
||
|
||
summarySheet.Cells[1, 32, 1, 36].Value = "ที่อยู่ที่สามารถติดต่อได้";
|
||
summarySheet.Cells[1, 32, 1, 36].Merge = true;
|
||
summarySheet.Cells[2, 32].Value = "เลขที่";
|
||
summarySheet.Cells[2, 33].Value = "ตำบล";
|
||
summarySheet.Cells[2, 34].Value = "อำเภอ";
|
||
summarySheet.Cells[2, 35].Value = "จังหวัด";
|
||
summarySheet.Cells[2, 36].Value = "รหัสไปรษณีย์";
|
||
|
||
summarySheet.Cells[1, 37, 1, 41].Value = "ที่อยู่ตามทะเบียนบ้าน";
|
||
summarySheet.Cells[1, 37, 1, 41].Merge = true;
|
||
summarySheet.Cells[2, 37].Value = "เลขที่";
|
||
summarySheet.Cells[2, 38].Value = "ตำบล";
|
||
summarySheet.Cells[2, 39].Value = "อำเภอ";
|
||
summarySheet.Cells[2, 40].Value = "จังหวัด";
|
||
summarySheet.Cells[2, 41].Value = "รหัสไปรษณีย์";
|
||
|
||
summarySheet.Cells[1, 42, 2, 42].Value = "บุคคลที่สามารถติดต่อได้";
|
||
summarySheet.Cells[1, 42, 2, 42].Merge = true;
|
||
summarySheet.Cells[1, 43, 2, 43].Value = "เกี่ยวข้องเป็น";
|
||
summarySheet.Cells[1, 43, 2, 43].Merge = true;
|
||
summarySheet.Cells[1, 44, 2, 44].Value = "โทรศัพท์";
|
||
summarySheet.Cells[1, 44, 2, 44].Merge = true;
|
||
summarySheet.Cells[1, 45, 2, 45].Value = "สถานะสอบคัดเลือก";
|
||
summarySheet.Cells[1, 45, 2, 45].Merge = true;
|
||
summarySheet.Cells[1, 46, 2, 46].Value = "ลำดับที่สอบได้";
|
||
summarySheet.Cells[1, 46, 2, 46].Merge = true;
|
||
summarySheet.Cells[1, 47, 2, 47].Value = "เลขประจำตัวสอบ";
|
||
summarySheet.Cells[1, 47, 2, 47].Merge = true;
|
||
summarySheet.Cells[1, 48, 2, 48].Value = "เลขที่นั่งสอบ";
|
||
summarySheet.Cells[1, 48, 2, 48].Merge = true;
|
||
summarySheet.Cells[1, 49, 2, 49].Value = "คะแนนเต็มภาค ก";
|
||
summarySheet.Cells[1, 49, 2, 49].Merge = true;
|
||
summarySheet.Cells[1, 50, 2, 50].Value = "คะแนนภาค ก";
|
||
summarySheet.Cells[1, 50, 2, 50].Merge = true;
|
||
summarySheet.Cells[1, 51, 2, 51].Value = "ผลสอบภาค ก";
|
||
summarySheet.Cells[1, 51, 2, 51].Merge = true;
|
||
summarySheet.Cells[1, 52, 2, 52].Value = "คะแนนเต็มภาค ข";
|
||
summarySheet.Cells[1, 52, 2, 52].Merge = true;
|
||
summarySheet.Cells[1, 53, 2, 53].Value = "คะแนนภาค ข";
|
||
summarySheet.Cells[1, 53, 2, 53].Merge = true;
|
||
summarySheet.Cells[1, 54, 2, 54].Value = "ผลสอบภาค ข";
|
||
summarySheet.Cells[1, 54, 2, 54].Merge = true;
|
||
summarySheet.Cells[1, 55, 2, 55].Value = "คะแนนเต็มภาค ค";
|
||
summarySheet.Cells[1, 55, 2, 55].Merge = true;
|
||
summarySheet.Cells[1, 56, 2, 56].Value = "คะแนนภาค ค";
|
||
summarySheet.Cells[1, 56, 2, 56].Merge = true;
|
||
summarySheet.Cells[1, 57, 2, 57].Value = "ผลสอบภาค ค";
|
||
summarySheet.Cells[1, 57, 2, 57].Merge = true;
|
||
summarySheet.Cells[1, 58, 2, 58].Value = "ผลการสอบ";
|
||
summarySheet.Cells[1, 58, 2, 58].Merge = true;
|
||
summarySheet.Cells[1, 59, 2, 59].Value = "วันที่สมัคร";
|
||
summarySheet.Cells[1, 59, 2, 59].Merge = true;
|
||
summarySheet.Cells[1, 60, 2, 60].Value = "วันและเวลาที่สมัคร";
|
||
summarySheet.Cells[1, 60, 2, 60].Merge = true;
|
||
int row = 3;
|
||
int num = 1;
|
||
|
||
foreach (var item in candidates)
|
||
{
|
||
var education = await _context.Educations
|
||
.AsQueryable()
|
||
.OrderBy(x => x.EducationEndDate)
|
||
.Where(x => x.Candidate.Id == item.Id)
|
||
.FirstOrDefaultAsync();
|
||
|
||
summarySheet.Cells[row, 1].Value = num;
|
||
summarySheet.Cells[row, 2].Value = item.PositionName + item.PositionLevelName;
|
||
summarySheet.Cells[row, 3].Value = item.FullName;
|
||
summarySheet.Cells[row, 4].Value = item.Nationality;
|
||
summarySheet.Cells[row, 5].Value = item.ReligionName;
|
||
summarySheet.Cells[row, 6].Value = item.DateOfBirth;
|
||
summarySheet.Cells[row, 7].Value = item.Age;
|
||
summarySheet.Cells[row, 8].Value = item.Telephone;
|
||
summarySheet.Cells[row, 9].Value = item.Email;
|
||
summarySheet.Cells[row, 10].Value = item.CitizenId;
|
||
|
||
summarySheet.Cells[row, 11].Value = education == null ? "-" : education.EducationLevelExamName;
|
||
summarySheet.Cells[row, 12].Value = education == null ? "-" : education.EducationName;
|
||
summarySheet.Cells[row, 13].Value = education == null ? "-" : education.EducationMajor;
|
||
summarySheet.Cells[row, 14].Value = education == null ? "-" : education.EducationLocation;
|
||
summarySheet.Cells[row, 15].Value = education == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate());
|
||
summarySheet.Cells[row, 16].Value = education == null ? "-" : education.EducationScores;
|
||
summarySheet.Cells[row, 17].Value = education == null ? "-" : education.EducationType;
|
||
summarySheet.Cells[row, 18].Value = education == null ? "-" : education.EducationLevelHighName;
|
||
|
||
summarySheet.Cells[row, 19].Value = item.OccupationPositionType == null ? null : GenerateStatusOccupation(item.OccupationPositionType);
|
||
summarySheet.Cells[row, 20].Value = item.OccupationPosition;
|
||
summarySheet.Cells[row, 21].Value = item.OccupationSalary;
|
||
summarySheet.Cells[row, 22].Value = item.OccupationGroup;
|
||
summarySheet.Cells[row, 23].Value = item.OccupationPile;
|
||
summarySheet.Cells[row, 24].Value = item.OccupationOrg;
|
||
summarySheet.Cells[row, 25].Value = item.OccupationTelephone;
|
||
|
||
summarySheet.Cells[row, 32].Value = item.CurrentAddress;
|
||
summarySheet.Cells[row, 33].Value = item.CurrentProvinceName;
|
||
summarySheet.Cells[row, 34].Value = item.CurrentDistrictName;
|
||
summarySheet.Cells[row, 35].Value = item.CurrentSubDistrictName;
|
||
summarySheet.Cells[row, 36].Value = item.CurrentZipCode;
|
||
|
||
summarySheet.Cells[row, 37].Value = item.RegistAddress;
|
||
summarySheet.Cells[row, 38].Value = item.RegistProvinceName;
|
||
summarySheet.Cells[row, 39].Value = item.RegistDistrictName;
|
||
summarySheet.Cells[row, 40].Value = item.RegistSubDistrictName;
|
||
summarySheet.Cells[row, 41].Value = item.RegistZipCode;
|
||
|
||
summarySheet.Cells[row, 42].Value = item.ContactFullName;
|
||
summarySheet.Cells[row, 43].Value = item.ContactRelations;
|
||
summarySheet.Cells[row, 44].Value = item.ContactTel;
|
||
summarySheet.Cells[row, 45].Value = GenerateStatusCandidate(item.Status);
|
||
|
||
summarySheet.Cells[row, 46].Value = item.Number;
|
||
summarySheet.Cells[row, 47].Value = item.ExamIdenNumber;
|
||
summarySheet.Cells[row, 48].Value = item.SeatNumber;
|
||
summarySheet.Cells[row, 49].Value = item.PointTotalA;
|
||
summarySheet.Cells[row, 50].Value = item.PointA;
|
||
summarySheet.Cells[row, 51].Value = item.ResultA;
|
||
summarySheet.Cells[row, 52].Value = item.PointTotalB;
|
||
summarySheet.Cells[row, 53].Value = item.PointB;
|
||
summarySheet.Cells[row, 54].Value = item.ResultB;
|
||
summarySheet.Cells[row, 55].Value = item.PointTotalC;
|
||
summarySheet.Cells[row, 56].Value = item.PointC;
|
||
summarySheet.Cells[row, 57].Value = item.ResultC;
|
||
summarySheet.Cells[row, 58].Value = item.Pass;
|
||
summarySheet.Cells[row, 59].Value = item.CreatedAt.Date.ToThaiShortDate();
|
||
summarySheet.Cells[row, 60].Value = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}";//xxxxxxxxxx
|
||
|
||
var careers = await _context.Careers
|
||
.AsQueryable()
|
||
.OrderBy(x => x.DurationStart)
|
||
.Where(x => x.Candidate.Id == item.Id)
|
||
.ToListAsync();
|
||
|
||
var yearDiff = 0;
|
||
var monthDiff = 0;
|
||
var dayDiff = 0;
|
||
var sb = new StringBuilder();
|
||
foreach (var career in careers)
|
||
{
|
||
if (career.DurationEnd < career.DurationStart)
|
||
{
|
||
var rangeObj = CalculateBetweenDateV2Value(career.DurationEnd, career.DurationStart);
|
||
yearDiff = yearDiff + rangeObj.years;
|
||
monthDiff = monthDiff + rangeObj.months;
|
||
dayDiff = dayDiff + rangeObj.days;
|
||
}
|
||
else
|
||
{
|
||
var rangeObj = CalculateBetweenDateV2Value(career.DurationStart, career.DurationEnd);
|
||
yearDiff = yearDiff + rangeObj.years;
|
||
monthDiff = monthDiff + rangeObj.months;
|
||
dayDiff = dayDiff + rangeObj.days;
|
||
}
|
||
if (dayDiff >= 30)
|
||
{
|
||
monthDiff = monthDiff + (int)(dayDiff / 30);
|
||
dayDiff = dayDiff % 30;
|
||
}
|
||
if (monthDiff >= 12)
|
||
{
|
||
yearDiff = yearDiff + (int)(monthDiff / 12);
|
||
monthDiff = monthDiff % 12;
|
||
}
|
||
sb.Clear();
|
||
sb.Append(yearDiff == 0 ? "" : $"{yearDiff} ปี ");
|
||
sb.Append(monthDiff == 0 ? "" : $"{monthDiff} เดือน ");
|
||
sb.Append(dayDiff == 0 ? "" : $"{dayDiff} วัน ");
|
||
}
|
||
var _career = false;
|
||
foreach (var career in careers)
|
||
{
|
||
summarySheet.Cells[row, 26].Value = career.Position;
|
||
summarySheet.Cells[row, 27].Value = career.Type;
|
||
summarySheet.Cells[row, 28].Value = career.DurationStart == null ? "-" : career.DurationStart.Date.ToThaiShortDate();
|
||
summarySheet.Cells[row, 29].Value = career.DurationEnd == null ? "-" : career.DurationEnd.Date.ToThaiShortDate();
|
||
summarySheet.Cells[row, 30].Value = career.RangeDate;
|
||
if (_career == false)
|
||
{
|
||
summarySheet.Cells[row, 31].Value = sb.ToString();
|
||
_career = true;
|
||
}
|
||
row++;
|
||
}
|
||
if (careers.Count == 0)
|
||
row++;
|
||
num = num + 1;
|
||
}
|
||
summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
|
||
package.Save();
|
||
}
|
||
|
||
stream.Position = 0;
|
||
return stream;
|
||
}
|
||
|
||
public async Task<dynamic> NewDownloadCandidateAllAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.Select(c => new
|
||
{
|
||
Id = c.Id,
|
||
Status = c.Status,
|
||
CitizenId = c.CitizenId,
|
||
FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
|
||
Nationality = c.Nationality,
|
||
DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
|
||
Age = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.CalculateAgeStrV2(0, 0),
|
||
ReligionName = c.ReligionName,
|
||
Telephone = c.Telephone,
|
||
MobilePhone = c.MobilePhone,
|
||
Email = c.Email,
|
||
Knowledge = c.Knowledge,
|
||
RegistAddress = c.RegistAddress,
|
||
RegistProvinceName = c.RegistProvinceName,
|
||
RegistDistrictName = c.RegistDistrictName,
|
||
RegistSubDistrictName = c.RegistSubDistrictName,
|
||
RegistZipCode = c.RegistZipCode,
|
||
CurrentAddress = c.CurrentAddress,
|
||
CurrentProvinceName = c.CurrentProvinceName,
|
||
CurrentDistrictName = c.CurrentDistrictName,
|
||
CurrentSubDistrictName = c.CurrentSubDistrictName,
|
||
CurrentZipCode = c.CurrentZipCode,
|
||
OccupationOrg = c.OccupationOrg,
|
||
OccupationPile = c.OccupationPile,
|
||
OccupationGroup = c.OccupationGroup,
|
||
OccupationSalary = c.OccupationSalary,
|
||
OccupationPosition = c.OccupationPosition,
|
||
OccupationPositionType = c.OccupationPositionType,
|
||
OccupationTelephone = c.OccupationTelephone,
|
||
RegisterDate = c.RegisterDate,
|
||
PositionName = c.PositionExam.PositionName,
|
||
PositionLevelName = c.PositionExam.PositionLevelName,
|
||
|
||
ContactFullName = $"{c.ContactPrefixName}{c.ContactFirstname} {c.ContactLastname}",
|
||
ContactRelations = c.ContactRelations,
|
||
ContactTel = c.ContactTel,
|
||
|
||
Number = c.Number,
|
||
ExamIdenNumber = c.ExamIdenNumber,
|
||
SeatNumber = c.SeatNumber,
|
||
PointTotalA = c.PointTotalA,
|
||
PointPath1A = c.PointPath1A,
|
||
PointPath2A = c.PointPath2A,
|
||
PointPath3A = c.PointPath3A,
|
||
PointA = c.PointA,
|
||
PointPerA = c.PointPerA,
|
||
ResultA = c.ResultA,
|
||
PointTotalB = c.PointTotalB,
|
||
PointB = c.PointB,
|
||
PointPerB = c.PointPerB,
|
||
ResultB = c.ResultB,
|
||
PointTotalC = c.PointTotalC,
|
||
PointPath1C = c.PointPath1C,
|
||
PointPath2C = c.PointPath2C,
|
||
PointC = c.PointC,
|
||
PointPerC = c.PointPerC,
|
||
ResultC = c.ResultC,
|
||
Pass = c.Pass,
|
||
ExamReason = c.ExamReason,
|
||
CreatedAt = c.CreatedAt,
|
||
})
|
||
.ToListAsync();
|
||
|
||
var _data = new List<object>();
|
||
if (candidates.Count > 0)
|
||
{
|
||
int num = 1;
|
||
foreach (var item in candidates)
|
||
{
|
||
var education = await _context.Educations
|
||
.AsQueryable()
|
||
.OrderBy(x => x.EducationEndDate)
|
||
.Where(x => x.Candidate.Id == item.Id)
|
||
.FirstOrDefaultAsync();
|
||
|
||
var careers = await _context.Careers
|
||
.AsQueryable()
|
||
.OrderBy(x => x.DurationStart)
|
||
.Where(x => x.Candidate.Id == item.Id)
|
||
.ToListAsync();
|
||
if (careers.Any())
|
||
{
|
||
var firstCareer = careers.First();
|
||
|
||
_data.Add(new
|
||
{
|
||
Num = num.ToString(),
|
||
PositionName = item.PositionName != null && item.PositionLevelName != null ? $"{item.PositionName} {item.PositionLevelName}" : "-",
|
||
FullName = item.FullName != null ? item.FullName: "-",
|
||
Nationality = item.Nationality != null ? item.Nationality : "-",
|
||
ReligionName = item.ReligionName != null ? item.ReligionName : "-",
|
||
DateOfBirth = item.DateOfBirth != null ? item.DateOfBirth : "-",
|
||
Age = item.Age != null ? item.Age : "-",
|
||
Telephone = item.Telephone != null ? item.Telephone : "-",
|
||
Email = item.Email != null ? item.Email : "-",
|
||
CitizenId = item.CitizenId != null ? item.CitizenId : "-",
|
||
|
||
EducationLevelExamName = education.EducationLevelExamName == null ? "-" : education.EducationLevelExamName,
|
||
EducationName = education.EducationName == null ? "-" : education.EducationName,
|
||
EducationMajor = education.EducationMajor == null ? "-" : education.EducationMajor,
|
||
EducationLocation = education.EducationLocation == null ? "-" : education.EducationLocation,
|
||
EducationEndDate = education.EducationEndDate == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate()),
|
||
EducationScores = education.EducationScores == null ? "-" : education.EducationScores,
|
||
EducationType = education.EducationType == null ? "-" : education.EducationType,
|
||
EducationLevelHighName = education.EducationLevelHighName == null ? "-" : education.EducationLevelHighName,
|
||
|
||
OccupationPositionType = item.OccupationPositionType == null ? "-" : GenerateStatusOccupation(item.OccupationPositionType),
|
||
OccupationPosition = item.OccupationPosition == null ? "-" : item.OccupationPosition,
|
||
OccupationSalary = item.OccupationSalary == null ? "-" : item.OccupationSalary.ToString(),
|
||
OccupationGroup = item.OccupationGroup == null ? "-" : item.OccupationGroup,
|
||
OccupationPile = item.OccupationPile == null ? "-" : item.OccupationPile,
|
||
OccupationOrg = item.OccupationOrg == null ? "-" : item.OccupationOrg,
|
||
OccupationTelephone = item.OccupationTelephone == null ? "-" : item.OccupationTelephone,
|
||
|
||
Position = firstCareer.Position == null ? "-" : firstCareer.Position,
|
||
Type = firstCareer.Type == null ? "-" : firstCareer.Type,
|
||
DurationStart = firstCareer.DurationStart == null ? "-" : firstCareer.DurationStart.Date.ToThaiShortDate(),
|
||
DurationEnd = firstCareer.DurationEnd == null ? "-" : firstCareer.DurationEnd.Date.ToThaiShortDate(),
|
||
RangeDate = firstCareer.RangeDate == null ? "-" : firstCareer.RangeDate,
|
||
|
||
CurrentAddress = item.CurrentAddress == null ? "-" : item.CurrentAddress,
|
||
CurrentProvinceName = item.CurrentProvinceName == null ? "-" : item.CurrentProvinceName,
|
||
CurrentDistrictName = item.CurrentDistrictName == null ? "-" : item.CurrentDistrictName,
|
||
CurrentSubDistrictName = item.CurrentSubDistrictName == null ? "-" : item.CurrentSubDistrictName,
|
||
CurrentZipCode = item.CurrentZipCode == null ? "-" : item.CurrentZipCode,
|
||
|
||
RegistAddress = item.RegistAddress == null ? "-" : item.RegistAddress,
|
||
RegistProvinceName = item.RegistProvinceName == null ? "-" : item.RegistProvinceName,
|
||
RegistDistrictName = item.RegistDistrictName == null ? "-" : item.RegistDistrictName,
|
||
RegistSubDistrictName = item.RegistSubDistrictName == null ? "-" : item.RegistSubDistrictName,
|
||
RegistZipCode = item.RegistZipCode == null ? "-" : item.RegistZipCode,
|
||
|
||
ContactFullName = item.ContactFullName == null ? "-" : item.ContactFullName,
|
||
ContactRelations = item.ContactRelations == null ? "-" : item.ContactRelations,
|
||
ContactTel = item.ContactTel == null ? "-" : item.ContactTel,
|
||
CandidateStatus = GenerateStatusCandidate(item.Status),
|
||
|
||
Number = item.Number == null ? "-" : item.Number,
|
||
ExamIdenNumber = item.ExamIdenNumber == null ? "-" : item.ExamIdenNumber,
|
||
SeatNumber = item.SeatNumber == null ? "-" : item.SeatNumber,
|
||
PointTotalA = item.PointTotalA == null ? "-" : item.PointTotalA,
|
||
PointA = item.PointA == null ? "-" : item.PointA,
|
||
ResultA = item.ResultA == null ? "-" : item.ResultA,
|
||
PointTotalB = item.PointTotalB == null ? "-" : item.PointTotalB,
|
||
PointB = item.PointB == null ? "-" : item.PointB,
|
||
ResultB = item.ResultB == null ? "-" : item.ResultB,
|
||
PointTotalC = item.PointTotalC == null ? "-" : item.PointTotalC,
|
||
PointC = item.PointC == null ? "-" : item.PointC,
|
||
ResultC = item.ResultC == null ? "-" : item.ResultC,
|
||
Pass = item.Pass == null ? "-" : item.Pass,
|
||
CreatedAt = item.CreatedAt.Date == null ? "-" : item.CreatedAt.Date.ToThaiShortDate(),
|
||
RegisterDate = item.RegisterDate.Value.Date == null ? "-" : $"{item.RegisterDate.Value.Date.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}"
|
||
});
|
||
|
||
if (careers.Count > 1)
|
||
{
|
||
foreach (var career in careers.Skip(1))
|
||
{
|
||
_data.Add(new
|
||
{
|
||
Num = "",
|
||
PositionName = "",
|
||
FullName = "",
|
||
Nationality = "",
|
||
ReligionName = "",
|
||
DateOfBirth = "",
|
||
Age = "",
|
||
Telephone = "",
|
||
Email = "",
|
||
CitizenId = "",
|
||
|
||
EducationLevelExamName = "",
|
||
EducationName = "",
|
||
EducationMajor = "",
|
||
EducationLocation = "",
|
||
EducationEndDate = "",
|
||
EducationScores = "",
|
||
EducationType = "",
|
||
EducationLevelHighName = "",
|
||
|
||
OccupationPositionType = "",
|
||
OccupationPosition = "",
|
||
OccupationSalary = "",
|
||
OccupationGroup = "",
|
||
OccupationPile = "",
|
||
OccupationOrg = "",
|
||
OccupationTelephone = "",
|
||
|
||
Position = career.Position != null ? career.Position : "-",
|
||
Type = career.Type != null ? career.Type : "-",
|
||
DurationStart = career.DurationStart != null ? career.DurationStart.Date.ToThaiShortDate() : "-",
|
||
DurationEnd = career.DurationEnd != null ? career.DurationEnd.Date.ToThaiShortDate() : "-",
|
||
RangeDate = career.RangeDate != null ? career.RangeDate : "-",
|
||
|
||
CurrentAddress = "",
|
||
CurrentProvinceName = "",
|
||
CurrentDistrictName = "",
|
||
CurrentSubDistrictName = "",
|
||
CurrentZipCode = "",
|
||
|
||
RegistAddress = "",
|
||
RegistProvinceName = "",
|
||
RegistDistrictName = "",
|
||
RegistSubDistrictName = "",
|
||
RegistZipCode = "",
|
||
|
||
ContactFullName = "",
|
||
ContactRelations = "",
|
||
ContactTel = "",
|
||
CandidateStatus = "",
|
||
|
||
Number = "",
|
||
ExamIdenNumber = "",
|
||
SeatNumber = "",
|
||
PointTotalA = "",
|
||
PointA = "",
|
||
ResultA = "",
|
||
PointTotalB = "",
|
||
PointB = "",
|
||
ResultB = "",
|
||
PointTotalC = "",
|
||
PointC = "",
|
||
ResultC = "",
|
||
Pass = "",
|
||
CreatedAt = "",
|
||
RegisterDate = ""
|
||
});
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
_data.Add(new
|
||
{
|
||
Num = num.ToString(),
|
||
PositionName = item.PositionName != null && item.PositionLevelName != null ? $"{item.PositionName} {item.PositionLevelName}" : "-",
|
||
FullName = item.FullName != null ? item.FullName : "-",
|
||
Nationality = item.Nationality != null ? item.Nationality : "-",
|
||
ReligionName = item.ReligionName != null ? item.ReligionName : "-",
|
||
DateOfBirth = item.DateOfBirth != null ? item.DateOfBirth : "-",
|
||
Age = item.Age != null ? item.Age : "-",
|
||
Telephone = item.Telephone != null ? item.Telephone : "-",
|
||
Email = item.Email != null ? item.Email : "-",
|
||
CitizenId = item.CitizenId != null ? item.CitizenId : "-",
|
||
|
||
EducationLevelExamName = education.EducationLevelExamName == null ? "-" : education.EducationLevelExamName,
|
||
EducationName = education.EducationName == null ? "-" : education.EducationName,
|
||
EducationMajor = education.EducationMajor == null ? "-" : education.EducationMajor,
|
||
EducationLocation = education.EducationLocation == null ? "-" : education.EducationLocation,
|
||
EducationEndDate = education.EducationEndDate == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate()),
|
||
EducationScores = education.EducationScores == null ? "-" : education.EducationScores,
|
||
EducationType = education.EducationType == null ? "-" : education.EducationType,
|
||
EducationLevelHighName = education.EducationLevelHighName == null ? "-" : education.EducationLevelHighName,
|
||
|
||
OccupationPositionType = item.OccupationPositionType == null ? "-" : GenerateStatusOccupation(item.OccupationPositionType),
|
||
OccupationPosition = item.OccupationPosition == null ? "-" : item.OccupationPosition,
|
||
OccupationSalary = item.OccupationSalary == null ? "-" : item.OccupationSalary.ToString(),
|
||
OccupationGroup = item.OccupationGroup == null ? "-" : item.OccupationGroup,
|
||
OccupationPile = item.OccupationPile == null ? "-" : item.OccupationPile,
|
||
OccupationOrg = item.OccupationOrg == null ? "-" : item.OccupationOrg,
|
||
OccupationTelephone = item.OccupationTelephone == null ? "-" : item.OccupationTelephone,
|
||
|
||
Position = "-",
|
||
Type = "-",
|
||
DurationStart = "-",
|
||
DurationEnd = "-",
|
||
RangeDate = "-",
|
||
|
||
CurrentAddress = item.CurrentAddress == null ? "-" : item.CurrentAddress,
|
||
CurrentProvinceName = item.CurrentProvinceName == null ? "-" : item.CurrentProvinceName,
|
||
CurrentDistrictName = item.CurrentDistrictName == null ? "-" : item.CurrentDistrictName,
|
||
CurrentSubDistrictName = item.CurrentSubDistrictName == null ? "-" : item.CurrentSubDistrictName,
|
||
CurrentZipCode = item.CurrentZipCode == null ? "-" : item.CurrentZipCode,
|
||
|
||
RegistAddress = item.RegistAddress == null ? "-" : item.RegistAddress,
|
||
RegistProvinceName = item.RegistProvinceName == null ? "-" : item.RegistProvinceName,
|
||
RegistDistrictName = item.RegistDistrictName == null ? "-" : item.RegistDistrictName,
|
||
RegistSubDistrictName = item.RegistSubDistrictName == null ? "-" : item.RegistSubDistrictName,
|
||
RegistZipCode = item.RegistZipCode == null ? "-" : item.RegistZipCode,
|
||
|
||
ContactFullName = item.ContactFullName == null ? "-" : item.ContactFullName,
|
||
ContactRelations = item.ContactRelations == null ? "-" : item.ContactRelations,
|
||
ContactTel = item.ContactTel == null ? "-" : item.ContactTel,
|
||
CandidateStatus = GenerateStatusCandidate(item.Status),
|
||
|
||
Number = item.Number == null ? "-" : item.Number,
|
||
ExamIdenNumber = item.ExamIdenNumber == null ? "-" : item.ExamIdenNumber,
|
||
SeatNumber = item.SeatNumber == null ? "-" : item.SeatNumber,
|
||
PointTotalA = item.PointTotalA == null ? "-" : item.PointTotalA,
|
||
PointA = item.PointA == null ? "-" : item.PointA,
|
||
ResultA = item.ResultA == null ? "-" : item.ResultA,
|
||
PointTotalB = item.PointTotalB == null ? "-" : item.PointTotalB,
|
||
PointB = item.PointB == null ? "-" : item.PointB,
|
||
ResultB = item.ResultB == null ? "-" : item.ResultB,
|
||
PointTotalC = item.PointTotalC == null ? "-" : item.PointTotalC,
|
||
PointC = item.PointC == null ? "-" : item.PointC,
|
||
ResultC = item.ResultC == null ? "-" : item.ResultC,
|
||
Pass = item.Pass == null ? "-" : item.Pass,
|
||
CreatedAt = item.CreatedAt.Date == null ? "-" : item.CreatedAt.Date.ToThaiShortDate(),
|
||
RegisterDate = item.RegisterDate.Value.Date == null ? "-" : $"{item.RegisterDate.Value.Date.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}"
|
||
});
|
||
}
|
||
|
||
num = num + 1;
|
||
}
|
||
}
|
||
return new
|
||
{
|
||
template = "candidateDetail",
|
||
reportName = $"CandidateDetail_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
|
||
data = new
|
||
{
|
||
data = _data
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<MemoryStream> GetsDashboardExamAsync(RequestCandidateDashboard item, string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
// .Where(x => x.CreatedAt.Date <= item.DateEnd.Date)
|
||
// .Where(x => x.CreatedAt.Date >= item.DateStart.Date)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.Select(c => new
|
||
{
|
||
Id = c.Id,
|
||
Status = c.Status,
|
||
CitizenId = c.CitizenId,
|
||
FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
|
||
Nationality = c.Nationality,
|
||
DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
|
||
Age = c.DateOfBirth == null ? 0 : DateTime.Now.Date.Year - c.DateOfBirth.Value.Date.Year,
|
||
ReligionName = c.ReligionName,
|
||
Telephone = c.Telephone,
|
||
MobilePhone = c.MobilePhone,
|
||
Email = c.Email,
|
||
Knowledge = c.Knowledge,
|
||
RegistAddress = c.RegistAddress,
|
||
RegistProvinceName = c.RegistProvinceName,
|
||
RegistDistrictName = c.RegistDistrictName,
|
||
RegistSubDistrictName = c.RegistSubDistrictName,
|
||
RegistZipCode = c.RegistZipCode,
|
||
CurrentAddress = c.CurrentAddress,
|
||
CurrentProvinceName = c.CurrentProvinceName,
|
||
CurrentDistrictName = c.CurrentDistrictName,
|
||
CurrentSubDistrictName = c.CurrentSubDistrictName,
|
||
CurrentZipCode = c.CurrentZipCode,
|
||
OccupationOrg = c.OccupationOrg,
|
||
OccupationPile = c.OccupationPile,
|
||
OccupationGroup = c.OccupationGroup,
|
||
OccupationSalary = c.OccupationSalary,
|
||
OccupationPosition = c.OccupationPosition,
|
||
OccupationPositionType = c.OccupationPositionType,
|
||
OccupationTelephone = c.OccupationTelephone,
|
||
Number = c.Number,
|
||
ExamIdenNumber = c.ExamIdenNumber,
|
||
SeatNumber = c.SeatNumber,
|
||
PointTotalA = c.PointTotalA,
|
||
PointPath1A = c.PointPath1A,
|
||
PointPath2A = c.PointPath2A,
|
||
PointPath3A = c.PointPath3A,
|
||
PointA = c.PointA,
|
||
PointPerA = c.PointPerA,
|
||
ResultA = c.ResultA,
|
||
PointTotalB = c.PointTotalB,
|
||
PointB = c.PointB,
|
||
PointPerB = c.PointPerB,
|
||
ResultB = c.ResultB,
|
||
PointTotalC = c.PointTotalC,
|
||
PointPath1C = c.PointPath1C,
|
||
PointPath2C = c.PointPath2C,
|
||
PointC = c.PointC,
|
||
PointPerC = c.PointPerC,
|
||
ResultC = c.ResultC,
|
||
Pass = c.Pass,
|
||
ExamReason = c.ExamReason,
|
||
CreatedAt = c.CreatedAt,
|
||
})
|
||
.ToListAsync();
|
||
|
||
var stream = new MemoryStream();
|
||
using (var package = new ExcelPackage(stream))
|
||
{
|
||
var summarySheet = package.Workbook.Worksheets.Add("Dashboard");
|
||
summarySheet.TabColor = System.Drawing.Color.Black;
|
||
// summarySheet.DefaultRowHeight = 17;
|
||
summarySheet.Column(1).Style.Font.Bold = true;
|
||
summarySheet.Row(4).Style.Font.Bold = true;
|
||
summarySheet.Row(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
summarySheet.Row(5).Style.Font.Bold = true;
|
||
summarySheet.Cells[1, 3].Style.Font.Bold = true;
|
||
int rowName = 2;
|
||
int rowCount = 2;
|
||
int fixMerge = 2;
|
||
// summarySheet.Cells[1, 1].Value = "ตั้งแต่";
|
||
// summarySheet.Cells[1, 2].Value = item.DateStart.Date.ToThaiShortDate();
|
||
// summarySheet.Cells[1, 3].Value = "ถึง";
|
||
// summarySheet.Cells[1, 4].Value = item.DateEnd.Date.ToThaiShortDate();
|
||
summarySheet.Cells[1, 1].Value = "ชื่อรอบการสอบ/ชื่อประกาศ";
|
||
summarySheet.Cells[1, 2].Value = periodExam.Name;
|
||
summarySheet.Cells[2, 1].Value = "ครั้งที่";
|
||
summarySheet.Cells[2, 2].Value = $"{periodExam.Round}/{periodExam.Year + 543}";
|
||
summarySheet.Cells[3, 1].Value = "จำนวนผู้สมัคร";
|
||
summarySheet.Cells[3, 2].Value = candidates.Count().ToString();
|
||
if (candidates.Count() > 0)
|
||
{
|
||
summarySheet.Cells[6, 1].Value = "จำนวน";
|
||
// summarySheet.Cells[1, 6].Value = "คะแนนภาค ข";
|
||
// summarySheet.Cells[1, 7].Value = "ผลสอบภาค ข";
|
||
// summarySheet.Cells[1, 8].Value = "คะแนนเต็มภาค ค";
|
||
// summarySheet.Cells[1, 9].Value = "คะแนนภาค ค";
|
||
// summarySheet.Cells[1, 10].Value = "ผลสอบภาค ค";
|
||
// summarySheet.Cells[1, 11].Value = "ผลการสอบ";
|
||
var educations = await _context.Educations
|
||
.AsQueryable()
|
||
.Where(x => x.Candidate.PeriodExam == periodExam)
|
||
.Where(x => x.Candidate.Status != "register")
|
||
// .Where(x => x.Candidate.CreatedAt.Date <= item.DateEnd.Date)
|
||
// .Where(x => x.Candidate.CreatedAt.Date >= item.DateStart.Date)
|
||
.GroupBy(x => x.EducationLevelExamName)
|
||
.Select(x => new
|
||
{
|
||
Name = x.Key,
|
||
Count = x.Count(),
|
||
})
|
||
.ToListAsync();
|
||
|
||
foreach (var education in educations)
|
||
{
|
||
summarySheet.Cells[5, rowName].Value = education.Name;
|
||
summarySheet.Cells[6, rowCount].Value = education.Count;
|
||
rowName++;
|
||
rowCount++;
|
||
}
|
||
if (educations.Count() != 0)
|
||
{
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "วุฒิที่ใช้สมัครสอบ";
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
|
||
fixMerge = rowCount;
|
||
}
|
||
var careers = await _context.Careers
|
||
.AsQueryable()
|
||
.Where(x => x.Candidate.PeriodExam == periodExam)
|
||
.Where(x => x.Candidate.Status != "register")
|
||
// .Where(x => x.Candidate.CreatedAt.Date <= item.DateEnd.Date)
|
||
// .Where(x => x.Candidate.CreatedAt.Date >= item.DateStart.Date)
|
||
.GroupBy(x => x.Position)
|
||
.Select(x => new
|
||
{
|
||
Name = x.Key,
|
||
Count = x.Count(),
|
||
})
|
||
.ToListAsync();
|
||
foreach (var career in careers)
|
||
{
|
||
summarySheet.Cells[5, rowName].Value = career.Name;
|
||
summarySheet.Cells[6, rowCount].Value = career.Count;
|
||
rowName++;
|
||
rowCount++;
|
||
}
|
||
if (careers.Count() != 0)
|
||
{
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "สถานที่ทำงาน/ฝึกงาน";
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
|
||
fixMerge = rowCount;
|
||
}
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 1-20 ปี";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 1 && x.Age <= 20).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 21-45 ปี";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 21 && x.Age <= 45).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 46-60 ปี";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 46 && x.Age <= 60).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 61-70 ปี";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 61 && x.Age <= 70).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 71-80 ปี";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 71 && x.Age <= 80).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[5, rowName].Value = "อายุ 80 ปีขึ้นไป";
|
||
summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 81).Count();
|
||
rowName++;
|
||
rowCount++;
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "ช่วงอายุ";
|
||
summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
|
||
}
|
||
else
|
||
{
|
||
summarySheet.Cells[6, 1].Value = "ไม่มีผู้สมัครสอบในช่วงเวลานี้";
|
||
}
|
||
summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
|
||
package.Save();
|
||
}
|
||
|
||
stream.Position = 0;
|
||
return stream;
|
||
}
|
||
|
||
public async Task<dynamic> NewGetsDashboardExamAsync(RequestCandidateDashboard item, string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.OrderBy(x => x.ExamIdenNumber)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.Select(c => new
|
||
{
|
||
Id = c.Id,
|
||
Status = c.Status,
|
||
CitizenId = c.CitizenId,
|
||
FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
|
||
Nationality = c.Nationality,
|
||
DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
|
||
Age = c.DateOfBirth == null ? 0 : DateTime.Now.Date.Year - c.DateOfBirth.Value.Date.Year,
|
||
ReligionName = c.ReligionName,
|
||
Telephone = c.Telephone,
|
||
MobilePhone = c.MobilePhone,
|
||
Email = c.Email,
|
||
Knowledge = c.Knowledge,
|
||
RegistAddress = c.RegistAddress,
|
||
RegistProvinceName = c.RegistProvinceName,
|
||
RegistDistrictName = c.RegistDistrictName,
|
||
RegistSubDistrictName = c.RegistSubDistrictName,
|
||
RegistZipCode = c.RegistZipCode,
|
||
CurrentAddress = c.CurrentAddress,
|
||
CurrentProvinceName = c.CurrentProvinceName,
|
||
CurrentDistrictName = c.CurrentDistrictName,
|
||
CurrentSubDistrictName = c.CurrentSubDistrictName,
|
||
CurrentZipCode = c.CurrentZipCode,
|
||
OccupationOrg = c.OccupationOrg,
|
||
OccupationPile = c.OccupationPile,
|
||
OccupationGroup = c.OccupationGroup,
|
||
OccupationSalary = c.OccupationSalary,
|
||
OccupationPosition = c.OccupationPosition,
|
||
OccupationPositionType = c.OccupationPositionType,
|
||
OccupationTelephone = c.OccupationTelephone,
|
||
Number = c.Number,
|
||
ExamIdenNumber = c.ExamIdenNumber,
|
||
SeatNumber = c.SeatNumber,
|
||
PointTotalA = c.PointTotalA,
|
||
PointPath1A = c.PointPath1A,
|
||
PointPath2A = c.PointPath2A,
|
||
PointPath3A = c.PointPath3A,
|
||
PointA = c.PointA,
|
||
PointPerA = c.PointPerA,
|
||
ResultA = c.ResultA,
|
||
PointTotalB = c.PointTotalB,
|
||
PointB = c.PointB,
|
||
PointPerB = c.PointPerB,
|
||
ResultB = c.ResultB,
|
||
PointTotalC = c.PointTotalC,
|
||
PointPath1C = c.PointPath1C,
|
||
PointPath2C = c.PointPath2C,
|
||
PointC = c.PointC,
|
||
PointPerC = c.PointPerC,
|
||
ResultC = c.ResultC,
|
||
Pass = c.Pass,
|
||
ExamReason = c.ExamReason,
|
||
CreatedAt = c.CreatedAt,
|
||
})
|
||
.ToListAsync();
|
||
|
||
var educations = new List<object>();
|
||
var careers = new List<object>();
|
||
var ages = new
|
||
{
|
||
one = "๐",
|
||
two = "๐",
|
||
three = "๐",
|
||
four = "๐",
|
||
five = "๐",
|
||
six = "๐",
|
||
};
|
||
var educationsCount = 0;
|
||
var careersCount = 0;
|
||
if (candidates.Count > 0)
|
||
{
|
||
educations = await _context.Educations
|
||
.AsQueryable()
|
||
.Where(x => x.Candidate.PeriodExam == periodExam)
|
||
.Where(x => x.Candidate.Status != "register")
|
||
.GroupBy(x => x.EducationLevelExamName)
|
||
.Select(x => (object)new
|
||
{
|
||
Name = x.Key,
|
||
ToTal = x.Count(),
|
||
Count = x.Count().ToString().ToThaiNumber(),
|
||
})
|
||
.ToListAsync();
|
||
|
||
educationsCount = educations.Sum(x => ((dynamic)x).ToTal);
|
||
|
||
careers = await _context.Careers
|
||
.AsQueryable()
|
||
.Where(x => x.Candidate.PeriodExam == periodExam)
|
||
.Where(x => x.Candidate.Status != "register")
|
||
.GroupBy(x => x.Position)
|
||
.Select(x => (object)new
|
||
{
|
||
Name = x.Key,
|
||
Total = x.Count(),
|
||
Count = x.Count().ToString().ToThaiNumber(),
|
||
})
|
||
.ToListAsync();
|
||
|
||
careersCount = careers.Sum(x => ((dynamic)x).Total);
|
||
|
||
ages = new
|
||
{
|
||
one = candidates.Where(x => x.Age >= 1 && x.Age <= 20).Count().ToString().ToThaiNumber(),
|
||
two = candidates.Where(x => x.Age >= 21 && x.Age <= 45).Count().ToString().ToThaiNumber(),
|
||
three = candidates.Where(x => x.Age >= 46 && x.Age <= 60).Count().ToString().ToThaiNumber(),
|
||
four = candidates.Where(x => x.Age >= 61 && x.Age <= 70).Count().ToString().ToThaiNumber(),
|
||
five = candidates.Where(x => x.Age >= 71 && x.Age <= 80).Count().ToString().ToThaiNumber(),
|
||
six = candidates.Where(x => x.Age >= 81).Count().ToString().ToThaiNumber(),
|
||
};
|
||
}
|
||
|
||
return new
|
||
{
|
||
template = "candidateDashboard",
|
||
reportName = $"สรุปข้อลการสมัครสอบ {periodExam.Name} ครั้งที่ {periodExam.Round}/{periodExam.Year.Value.ToThaiYear()}",
|
||
data = new
|
||
{
|
||
periodExamName = periodExam.Name.ToThaiNumber(),
|
||
periodExamRound = $"{periodExam.Round}/{periodExam.Year + 543}".ToThaiNumber(),
|
||
candidatesCount = candidates.Count().ToString().ToThaiNumber(),
|
||
educations = educations,
|
||
educationsCount = educationsCount.ToString().ToThaiNumber(),
|
||
careers = careers,
|
||
careersCount = careersCount.ToString().ToThaiNumber(),
|
||
ages = ages
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<MemoryStream> DownloadCandidatePaymentAllAsync(string examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == false)
|
||
.FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var candidates = await _context.Candidates
|
||
.AsQueryable()
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.Where(x => x.Status != "register")
|
||
.Select(c => new
|
||
{
|
||
CitizenId = c.CitizenId,
|
||
FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
|
||
PaymentDate = c.PaymentDate,
|
||
})
|
||
.ToListAsync();
|
||
|
||
var stream = new MemoryStream();
|
||
using (var package = new ExcelPackage(stream))
|
||
{
|
||
var summarySheet = package.Workbook.Worksheets.Add("Payment");
|
||
summarySheet.TabColor = System.Drawing.Color.Black;
|
||
summarySheet.Row(1).Style.Font.Bold = true;
|
||
summarySheet.Cells[1, 1].Value = "เลขบัตรประชาชน";
|
||
summarySheet.Cells[1, 2].Value = "ชื่อ-สกุล";
|
||
summarySheet.Cells[1, 3].Value = "ชำระค่าธรรมเนียม";
|
||
summarySheet.Cells[1, 4].Value = "วันเวลาชำระ";
|
||
int row = 2;
|
||
|
||
foreach (var item in candidates)
|
||
{
|
||
summarySheet.Cells[row, 1].Value = item.CitizenId;
|
||
summarySheet.Cells[row, 2].Value = item.FullName;
|
||
summarySheet.Cells[row, 3].Value = item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว";
|
||
summarySheet.Cells[row, 4].Value = item.PaymentDate == null ? "" : item.PaymentDate.Value;
|
||
row++;
|
||
}
|
||
summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
|
||
package.Save();
|
||
}
|
||
|
||
stream.Position = 0;
|
||
return stream;
|
||
}
|
||
|
||
public async Task UpdateAsyncCandidateToPlacement(Guid examId)
|
||
{
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Include(x => x.Candidate)
|
||
.ThenInclude(x => x.PositionExam)
|
||
.Include(x => x.Candidate)
|
||
.ThenInclude(x => x.ProfileImg)
|
||
.Include(x => x.Candidate)
|
||
.ThenInclude(x => x.Educations)
|
||
.FirstOrDefaultAsync(x => x.Id == examId);
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var _placement = await _contextMetadata.Placements.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.PlacementType.Name == "สอบคัดเลือก" && x.RefId == periodExam.Id);
|
||
if (_placement != null)
|
||
throw new Exception("รอบการสอบนี้ได้ทำการบรรจุไปแล้ว");
|
||
|
||
var placement = new Placement
|
||
{
|
||
Name = periodExam.Name,
|
||
RefId = periodExam.Id,
|
||
Round = periodExam.Round == null ? "" : periodExam.Round.ToString(),
|
||
Year = (int)(periodExam.Year == null ? 0 : periodExam.Year),
|
||
Number = periodExam.Candidate.Where(x => x.Status.Contains("done")).Count(),
|
||
PlacementType = await _contextMetadata.PlacementTypes.FirstOrDefaultAsync(x => x.Name.Trim().ToUpper().Contains("สอบคัดเลือก")) == null ? await _contextMetadata.PlacementTypes.FirstOrDefaultAsync() : await _contextMetadata.PlacementTypes.FirstOrDefaultAsync(x => x.Name.Trim().ToUpper().Contains("สอบคัดเลือก")),
|
||
StartDate = DateTime.Now,
|
||
EndDate = DateTime.Now.AddYears(2).AddDays(-1),
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _contextMetadata.Placements.AddAsync(placement);
|
||
foreach (var candidate in periodExam.Candidate.Where(x => x.Status.Trim().ToUpper() == "DONE" && x.Pass?.Trim() == "ได้"))
|
||
{
|
||
var IsOfficer = false;
|
||
dynamic org = null;
|
||
var apiUrl = $"{_configuration["API"]}/org/profile/citizenid/position/{candidate.CitizenId}";
|
||
using (var client = new HttpClient())
|
||
{
|
||
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token.Replace("Bearer ", ""));
|
||
client.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"]);
|
||
var _req = new HttpRequestMessage(HttpMethod.Get, apiUrl);
|
||
var _res = await client.SendAsync(_req);
|
||
var _result = await _res.Content.ReadAsStringAsync();
|
||
|
||
org = JsonConvert.DeserializeObject<dynamic>(_result);
|
||
|
||
if (org == null || org.result == null)
|
||
{
|
||
IsOfficer = false;
|
||
}
|
||
else
|
||
{
|
||
IsOfficer = true;
|
||
}
|
||
}
|
||
|
||
var placementProfile = new PlacementProfile
|
||
{
|
||
Placement = placement,
|
||
PositionCandidate = candidate?.PositionExam?.PositionName ?? null,
|
||
PositionLevel = candidate?.PositionExam?.PositionLevelName ?? null,
|
||
PositionType = candidate.PositionExam == null ? null : (candidate.PositionExam.HighDegree == null ? null : (candidate.PositionExam.HighDegree == true ? "วิชาการ" : "ทั่วไป")),
|
||
Prefix = candidate.PrefixName,
|
||
// ProfileImg = candidate.ProfileImg,
|
||
Firstname = candidate.FirstName,
|
||
Lastname = candidate.LastName,
|
||
Nationality = candidate.Nationality,
|
||
DateOfBirth = candidate.DateOfBirth,
|
||
Religion = candidate.ReligionName,
|
||
Email = candidate.Email,
|
||
CitizenId = candidate.CitizenId,
|
||
CitizenDistrictId = candidate.CitizenDistrictId,
|
||
CitizenProvinceId = candidate.CitizenProvinceId,
|
||
CitizenDate = candidate.CitizenDate,
|
||
Telephone = candidate.Telephone,
|
||
MobilePhone = candidate.MobilePhone,
|
||
Knowledge = candidate.Knowledge,
|
||
RegistAddress = candidate.RegistAddress,
|
||
RegistProvinceId = candidate.RegistProvinceId,
|
||
RegistDistrictId = candidate.RegistDistrictId,
|
||
RegistSubDistrictId = candidate.RegistSubDistrictId,
|
||
RegistZipCode = candidate.RegistZipCode,
|
||
RegistSame = candidate.RegistSame,
|
||
CurrentAddress = candidate.CurrentAddress,
|
||
CurrentProvinceId = candidate.CurrentProvinceId,
|
||
CurrentDistrictId = candidate.CurrentDistrictId,
|
||
CurrentSubDistrictId = candidate.CurrentSubDistrictId,
|
||
CurrentZipCode = candidate.CurrentZipCode,
|
||
Marry = candidate.Marry,
|
||
MarryPrefix = candidate.MarryPrefixName,
|
||
MarryFirstName = candidate.MarryFirstName,
|
||
MarryLastName = candidate.MarryLastName,
|
||
MarryOccupation = candidate.MarryOccupation,
|
||
MarryNationality = candidate.MarryNationality,
|
||
FatherPrefix = candidate.FatherPrefixName,
|
||
FatherFirstName = candidate.FatherFirstName,
|
||
FatherLastName = candidate.FatherLastName,
|
||
FatherOccupation = candidate.FatherOccupation,
|
||
FatherNationality = candidate.FatherNationality,
|
||
MotherPrefix = candidate.MotherPrefixName,
|
||
MotherFirstName = candidate.MotherFirstName,
|
||
MotherLastName = candidate.MotherLastName,
|
||
MotherOccupation = candidate.MotherOccupation,
|
||
MotherNationality = candidate.MotherNationality,
|
||
OccupationOrg = candidate.OccupationOrg,
|
||
OccupationPile = candidate.OccupationPile,
|
||
OccupationGroup = candidate.OccupationGroup,
|
||
OccupationSalary = candidate.OccupationSalary,
|
||
OccupationPosition = candidate.OccupationPosition,
|
||
OccupationPositionType = candidate.OccupationPositionType,
|
||
OccupationTelephone = candidate.OccupationTelephone,
|
||
PointTotalA = candidate.PointTotalA == null ? null : Convert.ToDouble(candidate.PointTotalA),
|
||
PointA = candidate.PointA == null ? null : Convert.ToDouble(candidate.PointA),
|
||
PointTotalB = candidate.PointTotalB == null ? null : Convert.ToDouble(candidate.PointTotalB),
|
||
PointB = candidate.PointB == null ? null : Convert.ToDouble(candidate.PointB),
|
||
PointTotalC = candidate.PointTotalC == null ? null : Convert.ToDouble(candidate.PointTotalC),
|
||
PointC = candidate.PointC == null ? null : Convert.ToDouble(candidate.PointC),
|
||
ExamNumber = candidate.Number == null ? null : Convert.ToInt32(candidate.Number),
|
||
ExamRound = 1,
|
||
IsRelief = false,
|
||
PlacementStatus = "UN-CONTAIN",
|
||
Pass = candidate.Pass,
|
||
RemarkHorizontal = "โดยมีเงื่อนไขว่าต้องปฏิบัติงานให้กรุงเทพมหานครเป็นระยะเวลาไม่น้อยกว่า ๕ ปี นับแต่วันที่ได้รับการบรรจุและแต่งตั้ง โดยห้ามโอนไปหน่วยงานหรือส่วนราชการอื่น เว้นเเต่ลาออกจากราชการ",
|
||
Amount = org?.result?.amount ?? null,
|
||
PositionSalaryAmount = org?.result?.positionSalaryAmount ?? null,
|
||
MouthSalaryAmount = org?.result?.mouthSalaryAmount ?? null,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
IsOfficer = IsOfficer,
|
||
profileId = org?.result?.profileId ?? null,
|
||
IsOld = org == null || org.result == null ? false : true,
|
||
AmountOld = org?.result?.AmountOld ?? null,
|
||
nodeOld = org?.result?.node ?? null,
|
||
nodeIdOld = org?.result?.nodeId ?? null,
|
||
posmasterIdOld = org?.result?.posmasterId ?? null,
|
||
rootOld = org?.result?.root ?? null,
|
||
rootIdOld = org?.result?.rootId ?? null,
|
||
rootShortNameOld = org?.result?.rootShortName ?? null,
|
||
child1Old = org?.result?.child1 ?? null,
|
||
child1IdOld = org?.result?.child1Id ?? null,
|
||
child1ShortNameOld = org?.result?.child1ShortName ?? null,
|
||
child2Old = org?.result?.child2 ?? null,
|
||
child2IdOld = org?.result?.child2Id ?? null,
|
||
child2ShortNameOld = org?.result?.child2ShortName ?? null,
|
||
child3Old = org?.result?.child3 ?? null,
|
||
child3IdOld = org?.result?.child3Id ?? null,
|
||
child3ShortNameOld = org?.result?.child3ShortName ?? null,
|
||
child4Old = org?.result?.child4 ?? null,
|
||
child4IdOld = org?.result?.child4Id ?? null,
|
||
child4ShortNameOld = org?.result?.child4ShortName ?? null,
|
||
orgRevisionIdOld = org?.result?.orgRevisionId ?? null,
|
||
posMasterNoOld = org?.result?.posMasterNo ?? null,
|
||
positionNameOld = org?.result?.position ?? null,
|
||
posTypeIdOld = org?.result?.posTypeId ?? null,
|
||
posTypeNameOld = org?.result?.posTypeName ?? null,
|
||
posLevelIdOld = org?.result?.posLevelId ?? null,
|
||
posLevelNameOld = org?.result?.posLevelName ?? null,
|
||
};
|
||
|
||
if (candidate.ProfileImg != null && candidate.ProfileImg.ObjectRefId != null)
|
||
{
|
||
IFormFile imageFile = await _minioService.GetImageToFormFileAsync(candidate.ProfileImg.ObjectRefId.ToString());
|
||
var doc = await _minioService.UploadImageFileAsync(imageFile, imageFile.FileName);
|
||
var _doc = await _contextMetadata.Documents.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.Id == doc.Id);
|
||
if (_doc != null)
|
||
{
|
||
placementProfile.ProfileImg = _doc;
|
||
}
|
||
}
|
||
|
||
await _contextMetadata.PlacementProfiles.AddAsync(placementProfile);
|
||
|
||
foreach (var education in candidate.Educations)
|
||
{
|
||
var placementEducation = new PlacementEducation
|
||
{
|
||
PlacementProfile = placementProfile,
|
||
EducationLevelId = education.EducationLevelExamId,
|
||
EducationLevelName = education.EducationLevelExamName,
|
||
Field = education.EducationMajor,
|
||
Gpa = education.EducationScores,
|
||
Institute = education.EducationLocation,
|
||
IsDate = true,
|
||
FinishDate = education.EducationEndDate,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _contextMetadata.PlacementEducations.AddAsync(placementEducation);
|
||
}
|
||
}
|
||
await _contextMetadata.SaveChangesAsync();
|
||
}
|
||
|
||
public async Task UpdateAsyncDisableToPlacement(Guid examId, DateTime accountStartDate)
|
||
{
|
||
try
|
||
{
|
||
// 🚀 Prepare HTTP client once via factory with timeout
|
||
var clientForPos = _httpClientFactory.CreateClient("default");
|
||
clientForPos.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token?.Replace("Bearer ", ""));
|
||
clientForPos.DefaultRequestHeaders.Remove("api_key");
|
||
clientForPos.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"] ?? "");
|
||
var apiUrl1 = $"{_configuration["API"]}/org/pos/level";
|
||
var response1 = string.Empty;
|
||
try
|
||
{
|
||
using var ctsPos = new CancellationTokenSource(TimeSpan.FromSeconds(30));
|
||
response1 = await clientForPos.GetStringAsync(apiUrl1, ctsPos.Token);
|
||
}
|
||
catch (TaskCanceledException)
|
||
{
|
||
// timeout - fallback to empty posOptions
|
||
response1 = string.Empty;
|
||
}
|
||
|
||
var posOptions = string.IsNullOrWhiteSpace(response1) ? null : JsonConvert.DeserializeObject<RecruitPosRequest>(response1);
|
||
|
||
var periodExam = await _context.PeriodExams.AsQueryable()
|
||
.Where(x => x.CheckDisability == true)
|
||
.FirstOrDefaultAsync(x => x.Id == examId);
|
||
|
||
if (periodExam == null)
|
||
throw new Exception(GlobalMessages.ExamNotFound);
|
||
|
||
var _placement = await _contextMetadata.Placements.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.PlacementType.Name == "คัดเลือกคนพิการ" && x.RefId == periodExam.Id);
|
||
if (_placement != null)
|
||
throw new Exception("รอบการสอบนี้ได้ทำการบรรจุไปแล้ว");
|
||
|
||
// 🚀 Pre-load all lookup data once
|
||
var placementTypesCache = await _contextMetadata.PlacementTypes.ToListAsync();
|
||
var provincesCache = await _contextOrg.province.ToListAsync();
|
||
var districtsCache = await _contextOrg.district.ToListAsync();
|
||
var subDistrictsCache = await _contextOrg.subDistrict.ToListAsync();
|
||
var educationLevelsCache = await _contextOrg.educationLevel.ToListAsync();
|
||
|
||
var placement = new Placement
|
||
{
|
||
Name = periodExam.Name,
|
||
RefId = periodExam.Id,
|
||
Round = periodExam.Round?.ToString() ?? "",
|
||
Year = (int)(periodExam.Year ?? 0),
|
||
Number = await _context.Disables.AsQueryable().Where(x => x.PeriodExam == periodExam).CountAsync(),
|
||
PlacementType = placementTypesCache.FirstOrDefault(x => x.Name.Trim().ToUpper().Contains("คัดเลือกคนพิการ")) ?? placementTypesCache.First(),
|
||
StartDate = accountStartDate,
|
||
EndDate = accountStartDate.AddYears(2).AddDays(-1),
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
await _contextMetadata.Placements.AddAsync(placement);
|
||
|
||
// 🚀 Load all related data with single queries
|
||
var candidates = await _context.Disables.AsQueryable()
|
||
.Include(x => x.Addresses)
|
||
.Include(x => x.Certificates)
|
||
.Include(x => x.Educations)
|
||
.Include(x => x.Occupations)
|
||
.Where(x => x.PeriodExam == periodExam)
|
||
.ToListAsync();
|
||
|
||
var scoreImport = await _context.ScoreImports.AsQueryable()
|
||
.FirstOrDefaultAsync(x => x.PeriodExam == periodExam);
|
||
|
||
var disableScores = await _context.DisableScores.AsQueryable()
|
||
.Where(x => x.ScoreImport == scoreImport && x.ExamStatus == "ผ่าน")
|
||
.ToListAsync();
|
||
|
||
var disableScoresDict = disableScores
|
||
.Where(x => !string.IsNullOrWhiteSpace(x.ExamId))
|
||
.ToDictionary(x => x.ExamId, x => x);
|
||
|
||
// 🚀 Batch HTTP requests using IHttpClientFactory with concurrency limit and cancellation
|
||
var clientForOrg = _httpClientFactory.CreateClient("default");
|
||
clientForOrg.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token?.Replace("Bearer ", ""));
|
||
clientForOrg.DefaultRequestHeaders.Remove("api_key");
|
||
clientForOrg.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"] ?? "");
|
||
|
||
var semaphore = new SemaphoreSlim(10); // limit concurrency
|
||
var orgTasks = candidates.Select(async candidate =>
|
||
{
|
||
if (string.IsNullOrWhiteSpace(candidate.CitizenId))
|
||
return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };
|
||
|
||
await semaphore.WaitAsync();
|
||
try
|
||
{
|
||
var apiUrl = $"{_configuration["API"]}/org/profile/citizenid/position/{candidate.CitizenId}";
|
||
try
|
||
{
|
||
using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
|
||
var response = await clientForOrg.GetStringAsync(apiUrl, cts.Token);
|
||
return new { CitizenId = candidate.CitizenId, org = JsonConvert.DeserializeObject<dynamic>(response) };
|
||
}
|
||
catch (TaskCanceledException)
|
||
{
|
||
// timeout
|
||
return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };
|
||
}
|
||
catch (Exception)
|
||
{
|
||
return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };
|
||
}
|
||
}
|
||
finally
|
||
{
|
||
semaphore.Release();
|
||
}
|
||
}).ToList();
|
||
|
||
var orgResults = await Task.WhenAll(orgTasks);
|
||
var orgDict = orgResults.ToDictionary(x => x.CitizenId ?? "", x => x.org);
|
||
|
||
// 🚀 Prepare batch inserts
|
||
var placementProfiles = new List<PlacementProfile>();
|
||
var placementEducations = new List<PlacementEducation>();
|
||
/*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
|
||
//var placementCertificates = new List<PlacementCertificate>();
|
||
|
||
foreach (var candidate in candidates)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(candidate.ExamId) ||
|
||
!disableScoresDict.TryGetValue(candidate.ExamId, out var disableScore))
|
||
continue;
|
||
|
||
var org = orgDict.TryGetValue(candidate.CitizenId ?? "", out var orgValue) ? orgValue : null;
|
||
var isOfficer = org?.result != null;
|
||
|
||
// 🚀 Cache repeated calculations
|
||
var firstAddress = candidate.Addresses?.FirstOrDefault();
|
||
var firstEducation = candidate.Educations?.FirstOrDefault();
|
||
var firstCertificate = candidate.Certificates?.FirstOrDefault();
|
||
var firstOccupation = candidate.Occupations?.FirstOrDefault();
|
||
|
||
var registAddress = string.Join("", new[] {
|
||
firstAddress?.Address ?? "",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Moo) ? "" : $" หมู่ {firstAddress.Moo}",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Soi) ? "" : $" ซอย {firstAddress.Soi}",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Road) ? "" : $" ถนน {firstAddress.Road}"
|
||
});
|
||
|
||
var currentAddress = string.Join("", new[] {
|
||
firstAddress?.Address1 ?? "",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Moo1) ? "" : $" หมู่ {firstAddress.Moo1}",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Soi1) ? "" : $" ซอย {firstAddress.Soi1}",
|
||
string.IsNullOrWhiteSpace(firstAddress?.Road1) ? "" : $" ถนน {firstAddress.Road1}"
|
||
});
|
||
|
||
// หาค่า posLevelName หลังสุด
|
||
var posLevelObject = posOptions?.result?.FirstOrDefault(x =>
|
||
!string.IsNullOrWhiteSpace(x.posLevelName) &&
|
||
!string.IsNullOrWhiteSpace(candidate.PositionName) &&
|
||
candidate.PositionName.Contains(x.posLevelName));
|
||
|
||
// เก็บเฉพาะค่า posLevelName
|
||
var posLevelName = posLevelObject?.posLevelName;
|
||
|
||
// สร้างตัวแปร PositionName ที่ตัดค่า posLevelName ออก
|
||
var positionNameWithoutLevel = candidate.PositionName ?? "";
|
||
if (!string.IsNullOrWhiteSpace(posLevelName))
|
||
{
|
||
positionNameWithoutLevel = positionNameWithoutLevel.Replace(posLevelName, "").Trim();
|
||
}
|
||
|
||
var placementProfile = new PlacementProfile
|
||
{
|
||
Placement = placement,
|
||
PositionCandidate = positionNameWithoutLevel ?? "",
|
||
PositionType = posLevelObject?.posTypes?.posTypeName ?? "",
|
||
PositionLevel = posLevelName ?? "",
|
||
Prefix = candidate.Prefix ?? "",
|
||
Firstname = candidate.FirstName ?? "",
|
||
Lastname = candidate.LastName ?? "",
|
||
Gender = candidate.Gendor ?? "",
|
||
Nationality = candidate.National ?? "",
|
||
Race = candidate.Race ?? "",
|
||
Religion = candidate.Religion ?? "",
|
||
DateOfBirth = candidate.DateOfBirth,
|
||
Relationship = candidate.Marry ?? "",
|
||
CitizenId = candidate.CitizenId ?? "",
|
||
CitizenProvinceId = provincesCache.FirstOrDefault(x => x.name == candidate.CitizenCardIssuer)?.Id,
|
||
CitizenDate = candidate.CitizenCardExpireDate,
|
||
Telephone = firstAddress?.Telephone ?? "",
|
||
MobilePhone = firstAddress?.Mobile ?? "",
|
||
RegistAddress = registAddress,
|
||
RegistProvinceId = provincesCache.FirstOrDefault(x => x.name == firstAddress?.Province)?.Id,
|
||
RegistDistrictId = districtsCache.FirstOrDefault(x => x.name == firstAddress?.District)?.Id,
|
||
RegistSubDistrictId = subDistrictsCache.FirstOrDefault(x => x.name == firstAddress?.Soi)?.Id,
|
||
RegistZipCode = firstAddress?.ZipCode ?? "",
|
||
RegistSame = false,
|
||
CurrentAddress = currentAddress,
|
||
CurrentProvinceId = provincesCache.FirstOrDefault(x => x.name == firstAddress?.Province1)?.Id,
|
||
CurrentDistrictId = districtsCache.FirstOrDefault(x => x.name == firstAddress?.District1)?.Id,
|
||
CurrentSubDistrictId = subDistrictsCache.FirstOrDefault(x => x.name == firstAddress?.Soi1)?.Id,
|
||
CurrentZipCode = firstAddress?.ZipCode1,
|
||
Marry = candidate.Marry?.Contains("สมรส") ?? false,
|
||
OccupationPositionType = "other",
|
||
OccupationTelephone = firstOccupation?.Telephone ?? "",
|
||
OccupationPosition = firstOccupation?.Position ?? "",
|
||
PointTotalA = Convert.ToDouble(disableScore.FullA),
|
||
PointA = Convert.ToDouble(disableScore.SumA),
|
||
PointTotalB = Convert.ToDouble(disableScore.FullB),
|
||
PointB = Convert.ToDouble(disableScore.SumB),
|
||
PointTotalC = Convert.ToDouble(disableScore.FullC),
|
||
PointC = Convert.ToDouble(disableScore.SumC),
|
||
ExamNumber = !string.IsNullOrWhiteSpace(disableScore.Number) && int.TryParse(disableScore.Number, out int n) ? n : null,
|
||
ExamRound = null,
|
||
IsRelief = false,
|
||
PlacementStatus = "UN-CONTAIN",
|
||
Pass = disableScore.ExamStatus ?? "",
|
||
RemarkHorizontal = "โดยมีเงื่อนไขว่าต้องปฏิบัติงานให้กรุงเทพมหานครเป็นระยะเวลาไม่น้อยกว่า ๕ ปี นับแต่วันที่ได้รับการบรรจุและแต่งตั้ง โดยห้ามโอนไปหน่วยงานหรือส่วนราชการอื่น เว้นเเต่ลาออกจากราชการ",
|
||
Amount = org?.result?.amount,
|
||
PositionSalaryAmount = org?.result?.positionSalaryAmount,
|
||
MouthSalaryAmount = org?.result?.mouthSalaryAmount,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
IsOfficer = isOfficer,
|
||
profileId = org?.result?.profileId ?? "",
|
||
IsOld = org?.result != null,
|
||
AmountOld = org?.result?.AmountOld,
|
||
nodeOld = org?.result?.node ?? "",
|
||
nodeIdOld = org?.result?.nodeId ?? "",
|
||
posmasterIdOld = org?.result?.posmasterId ?? "",
|
||
rootOld = org?.result?.root ?? "",
|
||
rootIdOld = org?.result?.rootId ?? "",
|
||
rootShortNameOld = org?.result?.rootShortName ?? "",
|
||
child1Old = org?.result?.child1 ?? "",
|
||
child1IdOld = org?.result?.child1Id ?? "",
|
||
child1ShortNameOld = org?.result?.child1ShortName ?? "",
|
||
child2Old = org?.result?.child2 ?? "",
|
||
child2IdOld = org?.result?.child2Id ?? "",
|
||
child2ShortNameOld = org?.result?.child2ShortName ?? "",
|
||
child3Old = org?.result?.child3 ?? "",
|
||
child3IdOld = org?.result?.child3Id ?? "",
|
||
child3ShortNameOld = org?.result?.child3ShortName ?? "",
|
||
child4Old = org?.result?.child4 ?? "",
|
||
child4IdOld = org?.result?.child4Id ?? "",
|
||
child4ShortNameOld = org?.result?.child4ShortName ?? "",
|
||
orgRevisionIdOld = org?.result?.orgRevisionId ?? "",
|
||
posMasterNoOld = org?.result?.posMasterNo,
|
||
positionNameOld = org?.result?.position ?? "",
|
||
posTypeIdOld = org?.result?.posTypeId ?? "",
|
||
posTypeNameOld = org?.result?.posTypeName ?? "",
|
||
posLevelIdOld = org?.result?.posLevelId ?? "",
|
||
posLevelNameOld = org?.result?.posLevelName ?? "",
|
||
};
|
||
placementProfiles.Add(placementProfile);
|
||
|
||
var placementEducation = new PlacementEducation
|
||
{
|
||
PlacementProfile = placementProfile,
|
||
EducationLevelId = educationLevelsCache.FirstOrDefault(x => x.name == firstEducation?.HighDegree)?.Id,
|
||
EducationLevelName = educationLevelsCache.FirstOrDefault(x => x.name == firstEducation?.HighDegree)?.name,
|
||
Field = firstEducation?.Major ?? "",
|
||
Gpa = firstEducation?.GPA.ToString() ?? "",
|
||
Institute = firstEducation?.University ?? "",
|
||
Degree = firstEducation?.Degree ?? "",
|
||
FinishDate = firstEducation?.BachelorDate,
|
||
IsDate = true,
|
||
CreatedAt = DateTime.Now,
|
||
CreatedUserId = UserId ?? "",
|
||
LastUpdatedAt = DateTime.Now,
|
||
LastUpdateUserId = UserId ?? "",
|
||
CreatedFullName = FullName ?? "",
|
||
LastUpdateFullName = FullName ?? "",
|
||
};
|
||
placementEducations.Add(placementEducation);
|
||
/*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
|
||
//var placementCertificate = new PlacementCertificate
|
||
//{
|
||
// PlacementProfile = placementProfile,
|
||
// CertificateNo = firstCertificate?.CertificateNo ?? "",
|
||
// IssueDate = firstCertificate?.IssueDate,
|
||
// ExpireDate = firstCertificate?.ExpiredDate,
|
||
// CertificateType = firstCertificate?.Description ?? "",
|
||
// CreatedAt = DateTime.Now,
|
||
// CreatedUserId = UserId ?? "",
|
||
// LastUpdatedAt = DateTime.Now,
|
||
// LastUpdateUserId = UserId ?? "",
|
||
// CreatedFullName = FullName ?? "",
|
||
// LastUpdateFullName = FullName ?? "",
|
||
//};
|
||
//placementCertificates.Add(placementCertificate);
|
||
}
|
||
|
||
// 🚀 Batch insert all records
|
||
await _contextMetadata.PlacementProfiles.AddRangeAsync(placementProfiles);
|
||
await _contextMetadata.PlacementEducations.AddRangeAsync(placementEducations);
|
||
/*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
|
||
//await _contextMetadata.PlacementCertificates.AddRangeAsync(placementCertificates);
|
||
|
||
// 🚀 Single SaveChanges at the end
|
||
await _contextMetadata.SaveChangesAsync();
|
||
}
|
||
catch
|
||
{
|
||
throw;
|
||
}
|
||
}
|
||
|
||
|
||
public CalculateBetweenDateV2ValueObj CalculateBetweenDateV2Value(DateTime startDate, DateTime endDate)
|
||
{
|
||
// if (startDate == null || endDate == null)
|
||
// return null;
|
||
DateTime today = endDate.AddDays(1);
|
||
DateTime birthDate = startDate;
|
||
int years = new DateTime(endDate.Subtract(birthDate).Ticks).Year - 1;
|
||
DateTime pastYearDate = birthDate.AddYears(years);
|
||
int months = 0;
|
||
for (int i = 1; i <= 12; i++)
|
||
{
|
||
if (pastYearDate.AddMonths(i) == today)
|
||
{
|
||
months = i;
|
||
break;
|
||
}
|
||
else if (pastYearDate.AddMonths(i) >= today)
|
||
{
|
||
months = i - 1;
|
||
break;
|
||
}
|
||
}
|
||
int days = today.Subtract(pastYearDate.AddMonths(months)).Days;
|
||
if (today.Day < pastYearDate.Day)
|
||
{
|
||
if (System.DateTime.DaysInMonth(pastYearDate.Year, pastYearDate.Month) > System.DateTime.DaysInMonth(pastYearDate.AddMonths(months).Year, pastYearDate.AddMonths(months).Month))
|
||
{
|
||
days += 1;
|
||
}
|
||
if (System.DateTime.DaysInMonth(pastYearDate.Year, pastYearDate.Month) < System.DateTime.DaysInMonth(pastYearDate.AddMonths(months).Year, pastYearDate.AddMonths(months).Month))
|
||
{
|
||
days -= 1;
|
||
}
|
||
}
|
||
if (days >= 30)
|
||
{
|
||
months = months + 1;
|
||
days = 0;
|
||
if (months >= 12)
|
||
{
|
||
years = years + 1;
|
||
months = 0;
|
||
}
|
||
}
|
||
|
||
var sb = new StringBuilder();
|
||
sb.Clear();
|
||
sb.Append(years == 0 ? "" : $"{years} ปี ");
|
||
sb.Append(months == 0 ? "" : $"{months} เดือน ");
|
||
sb.Append(days == 0 ? "" : $"{days} วัน ");
|
||
return new CalculateBetweenDateV2ValueObj { years = years, months = months, days = days };
|
||
}
|
||
public class CalculateBetweenDateV2ValueObj
|
||
{
|
||
public int years { get; set; }
|
||
|
||
public int months { get; set; }
|
||
|
||
public int days { get; set; }
|
||
}
|
||
#endregion
|
||
}
|
||
}
|