public async Task <IActionResult> GetCertificateByPersonId(int id)
{
var tempCertificate = await _certificateService.GetCertificateByPersonId(id);
apiResult.AppResult.Message = Constant.GET_SUCCESS;
apiResult.AppResult.DataResult = tempCertificate;
return(Ok(apiResult.AppResult));
}
public async Task <AppResult> ImportFile(FileUpload file, int?id)
{
AppResult appResult = new AppResult();
var fileName = file.files.FileName;
if (!ImportFileHandling.HasFileExtension(Path.GetExtension(fileName)))
{
appResult.Result = false;
appResult.Message = "CV Import Failed, Invalid file format!";
return(appResult);
}
var list = await ImportFileHandling.GetInformationCV(file.files);
if (list.Count > 0)
{
var resultCheckFile = ImportFileHandling.CheckFile(list);
if (resultCheckFile.Result)
{
int idPerson = 0;
List <SkillRequestModel> listSkill = new List <SkillRequestModel>();
List <int> listIdWorkHistory = new List <int>();
List <SaveWorkHistoryResource> saveWorkHistoryResources = new List <SaveWorkHistoryResource>();
List <int> listIdEducation = new List <int>();
List <SaveEducationResource> saveEducationResources = new List <SaveEducationResource>();
List <int> listIdCertificate = new List <int>();
List <SaveCertificateResource> saveCertificateResources = new List <SaveCertificateResource>();
List <int> listIdSkill = new List <int>();
List <SkillResource> skillRequestModels = new List <SkillResource>();
List <int> listIdProject = new List <int>();
List <Project> listProject = new List <Project>();
try
{
using (TransactionScope txScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
Person person = ImportFileHandling.GetPerson(list);
Image image = await ImportFileHandling.GetImageCV(file.files);
if (id == null)
{
var resultPerson = await _personService.InsertPersonToImportFile(person, image);
appResult = resultPerson.AppResult;
idPerson = resultPerson.PersonInfo.Id;
}
else
{
idPerson = (int)id;
if (idPerson > 0)
{
var tempPerson = await _personService.GetPersonById(idPerson);
var modelPerson = (Person)tempPerson;
if (modelPerson != null)
{
person.Id = (int)id;
var resultPerson = await _personService.UpdatePersonToImportFile(person, image);
appResult = resultPerson.AppResult;
if (appResult.Result)
{
var listWorkHistoryResourceByPersonId = await _workHistoryService.GetWorkHistoryByPersonId(idPerson);
saveWorkHistoryResources = listWorkHistoryResourceByPersonId.ToList();
if (saveWorkHistoryResources != null)
{
foreach (var item in saveWorkHistoryResources)
{
listIdWorkHistory.Add(item.Id);
}
if (listIdWorkHistory != null)
{
foreach (var item in listIdWorkHistory)
{
await _workHistoryService.DeleteWorkHistory(item);
}
}
}
var listEducationByPersonId = await _educationService.GetEducationByPersonId(idPerson);
saveEducationResources = listEducationByPersonId.ToList();
if (saveEducationResources != null)
{
foreach (var item in saveEducationResources)
{
listIdEducation.Add(item.Id);
}
if (listIdEducation != null)
{
foreach (var item in listIdEducation)
{
await _educationService.DeleteEducation(item);
}
}
}
var listCertificateByPersonId = await _certificateService.GetCertificateByPersonId(idPerson);
saveCertificateResources = listCertificateByPersonId.ToList();
if (saveCertificateResources != null)
{
foreach (var item in saveCertificateResources)
{
listIdCertificate.Add(item.Id);
}
if (listIdCertificate != null)
{
foreach (var item in listIdCertificate)
{
await _certificateService.DeleteCertificate(item);
}
}
}
var listSkillByPeronId = await _skillService.GetSkillByPerson(idPerson);
skillRequestModels = listSkillByPeronId.ToList();
if (skillRequestModels != null)
{
foreach (var item in skillRequestModels)
{
listIdSkill.Add(item.PersonCategoryId);
}
if (listIdSkill != null)
{
foreach (var item in listIdSkill)
{
await _skillService.DeleteSkill(item);
}
}
}
var listProjectByPersonId = await _projectService.GetProjectByPersonId(idPerson);
listProject = listProjectByPersonId.ToList();
if (listProject != null)
{
foreach (var item in listProject)
{
listIdProject.Add(item.Id);
}
if (listIdProject != null)
{
foreach (var item in listIdProject)
{
await _projectService.DeleteProject(item);
}
}
}
}
idPerson = resultPerson.PersonInfo.Id;
}
else
{
appResult.Result = false;
appResult.Message = "CV Import Failed, CV not exist";
return(appResult);
}
}
}
if (idPerson > 0)
{
List <CreateWorkHistoryResource> workHistoryResources = ImportFileHandling.GetListWorkHistory(list, idPerson);
if (workHistoryResources != null)
{
foreach (var item in workHistoryResources)
{
if (item != null)
{
await _workHistoryService.CreateWorkHistory(item);
}
}
}
List <CreateEducationResource> createEducationResources = ImportFileHandling.GetListEducation(list, idPerson);
if (createEducationResources != null)
{
foreach (var item in createEducationResources)
{
if (item != null)
{
await _educationService.CreateEducation(item);
}
}
}
List <CreateCertificateResource> createCertificateResources = ImportFileHandling.GetListCertifiate(list, idPerson);
if (createCertificateResources != null)
{
foreach (var item in createCertificateResources)
{
if (item != null)
{
await _certificateService.CreateCertificate(item);
}
}
}
List <Category> categories = ImportFileHandling.GetListCategory(list);
List <int> Category = new List <int>();
if (categories != null)
{
listSkill = new List <SkillRequestModel>();
foreach (var groupItem in categories)
{
var resultCategory = await _categoryService.InsertCategory(groupItem);
int idCategory = resultCategory.Category.Id;
Category.Add(idCategory);
List <int> listTechnology = new List <int>();
foreach (var item in groupItem.Technologies)
{
item.CategoryId = idCategory;
var resultTechnology = await _technologyService.InsertTechnology(item);
int idTechnology = resultTechnology.Technology.Id;
listTechnology.Add(idTechnology);
}
SkillRequestModel skill = new SkillRequestModel
{
PersonId = idPerson,
CategoryId = idCategory,
TechnologyId = listTechnology
};
listSkill.Add(skill);
}
if (listSkill.Count > 0)
{
foreach (var item in listSkill)
{
await _skillService.InserSkill(item);
}
}
}
List <Project> projects = ImportFileHandling.GetListProject(list, idPerson);
if (projects != null)
{
foreach (var gorupItem in projects)
{
List <ProjectTechnology> projectTechnologies = new List <ProjectTechnology>();
var resultProject = await _projectService.InsertProject(gorupItem);
int idProject = resultProject.Project.Id;
foreach (var item in gorupItem.Technologies)
{
ProjectTechnology projectTechnology = new ProjectTechnology
{
ProjectId = idProject,
TechnologyId = await _technologyService.GetTechnologyByPersonAndNameAsync(idPerson, item.Name)
};
projectTechnologies.Add(projectTechnology);
}
await _projectTechnologyService.InsertListTechnologyAsync(projectTechnologies);
}
}
}
txScope.Complete();
}
}
catch
{
appResult.Result = false;
appResult.Message = "CV Import Failed, file is invalid!";
}
}
else
{
appResult.Result = false;
appResult.Message = resultCheckFile.Message;
}
}
else
{
appResult.Result = false;
appResult.Message = "CV Import Failed, file is invalid!";
}
return(appResult);
}
public async Task <string> ExportFile(int id, string type)
{
await Task.Yield();
var profilePerson = await _personService.GetPersonById(id);
var listSkill = await _skillService.GetSkillByPerson(id);
var listWorkHistory = await _workHistoryService.GetWorkHistoryByPersonId(id);
var listEducation = await _educationService.GetEducationByPersonId(id);
var listCertificate = await _certificateService.GetCertificateByPersonId(id);
var listProject = await _projectService.GetProjectByPersonId(id);
if (profilePerson == null)
{
return(null);
}
ExportFileUtils exportFileUtils = new ExportFileUtils();
string fullPathTemplate = $@"{_webHostEnvironment.WebRootPath}/Form_CV.xlsx";
string sheetName = "Sheet1";
using (var source = System.IO.File.OpenRead(fullPathTemplate))
using (ExcelPackage excelPkg = new ExcelPackage(source))
{
ExcelWorksheet worksheet = exportFileUtils.SheetTemPlate(excelPkg, sheetName);
int irecordIndex = 3;
StringBuilder strBuilder = new StringBuilder(string.Empty);
string defaultSpace = " ";
#region Export profile Person
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#bdd6ee");
worksheet.Cells[$"B{irecordIndex}:M{irecordIndex}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[$"B{irecordIndex}"].Value = profilePerson.FullName;
worksheet.Cells[$"B{irecordIndex + 1}"].Value = defaultSpace + "Office";
worksheet.Cells[$"D{irecordIndex + 1}"].Value = defaultSpace + profilePerson.Location;
worksheet.Cells[$"B{irecordIndex + 2}"].Value = defaultSpace + "Gender";
worksheet.Cells[$"D{irecordIndex + 2}"].Value = defaultSpace + profilePerson.Gender;
worksheet.Cells[$"B{irecordIndex + 3}"].Value = defaultSpace + "Year of Birth";
worksheet.Cells[$"D{irecordIndex + 3}"].Value = defaultSpace + profilePerson.YearOfBirth.Year;
worksheet.Cells[$"B{irecordIndex + 4}:N{irecordIndex + 4}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[$"B{irecordIndex + 5}"].Value = profilePerson.Description;
irecordIndex += 5;
#endregion
#region Export list Skill
int irecordSkill = irecordIndex + 4;
int itemSkill = listSkill.ToNonNullList().Count;
if (irecordSkill + itemSkill <= irecordIndex + 14)
{
worksheet.DeleteRow(irecordSkill, 10 - itemSkill, false);
}
else if (irecordSkill + itemSkill - 10 >= irecordIndex + 4)
{
worksheet.InsertRow(irecordSkill + 10, itemSkill - 10, irecordSkill);
}
worksheet.Cells[$"B{irecordSkill - 1}:N{irecordSkill - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listSkill)
{
worksheet.Cells[$"B{irecordSkill}"].Value = defaultSpace + item.Name;
int lengthTemp = item.Technologies.Count;
for (int i = 0; i < lengthTemp; i++)
{
if (i == (lengthTemp - 1))
{
strBuilder.Append($"{item.Technologies[i].Name}");
break;
}
strBuilder.Append($"{item.Technologies[i].Name}, ");
}
worksheet.Cells[$"D{irecordSkill}"].Value = defaultSpace + strBuilder.ToString();
strBuilder.Clear();
irecordSkill++;
}
#endregion
#region Export list WorkHistory
int irecordWorkHistory = irecordSkill + 2;
int irecordNumber = 1;
int itemWorkHistory = listWorkHistory.ToNonNullList().Count;
if (irecordWorkHistory + itemWorkHistory <= irecordSkill + 12)
{
worksheet.DeleteRow(irecordWorkHistory, 10 - itemWorkHistory, true);
}
else if (irecordWorkHistory + itemWorkHistory - 10 >= irecordSkill + 2)
{
worksheet.InsertRow(irecordWorkHistory + 10, itemWorkHistory - 10, irecordWorkHistory);
}
worksheet.Cells[$"B{irecordWorkHistory - 2}:N{irecordWorkHistory - 2}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listWorkHistory)
{
worksheet.Cells[$"B{irecordWorkHistory}"].Value = irecordNumber.ToString();
worksheet.Cells[$"C{irecordWorkHistory}"].Value = item.EndDate + "-" + item.StartDate;
worksheet.Cells[$"F{irecordWorkHistory}"].Value = item.CompanyName;
worksheet.Cells[$"M{irecordWorkHistory}"].Value = item.Position;
irecordNumber++;
irecordWorkHistory++;
}
#endregion
#region Export list Education
int irecordEducation = irecordWorkHistory + 1;
int itemEducation = listEducation.ToNonNullList().Count;
if (irecordEducation + 2 * itemEducation <= irecordWorkHistory + 13)
{
worksheet.DeleteRow(irecordEducation, 12 - 2 * itemEducation, true);
}
else if (irecordEducation + 2 * itemEducation - 12 >= irecordWorkHistory + 1)
{
worksheet.InsertRow(irecordEducation, 2 * itemEducation - 12, irecordEducation);
}
worksheet.Cells[$"B{irecordEducation - 1}:N{irecordEducation - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listEducation)
{
worksheet.Cells[$"B{irecordEducation}"].Value = defaultSpace + item.EndDate + " - " + item.StartDate + " | " + item.CollegeName;
worksheet.Cells[$"B{irecordEducation + 1}"].Value = defaultSpace + "Major: " + item.Major;
irecordEducation += 2;
}
#endregion
#region Export list Certificate
int irecordCertificate = irecordEducation + 1;
int itemCertificate = listCertificate.ToNonNullList().Count;
if (irecordCertificate + itemCertificate <= irecordEducation + 11)
{
worksheet.DeleteRow(irecordCertificate, 10 - itemCertificate, true);
}
else if (irecordCertificate + itemCertificate - 10 >= irecordEducation + 1)
{
worksheet.InsertRow(irecordCertificate + 10, itemCertificate - 10, irecordEducation);
}
worksheet.Cells[$"B{irecordCertificate - 1}:N{irecordCertificate - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listCertificate)
{
worksheet.Cells[$"B{irecordCertificate}"].Value = defaultSpace + item.StartDate + " | " + item.Name + " - " + item.Provider;
irecordCertificate++;
}
#endregion
#region Export list Project
int irecordProject = irecordCertificate + 2;
irecordNumber = 1;
int itemProject = listProject.ToNonNullList().Count;
if (irecordProject + 5 * itemProject <= irecordEducation + 42)
{
worksheet.DeleteRow(irecordProject, 40 - 5 * itemProject, true);
}
else if (irecordProject + 5 * itemProject - 40 >= irecordCertificate + 2)
{
worksheet.InsertRow(irecordProject, 5 * itemProject - 40, irecordEducation);
}
worksheet.Cells[$"B{irecordProject - 2}:N{irecordProject - 2}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listProject)
{
worksheet.Cells[$"B{irecordProject}"].Value = irecordNumber.ToString();
worksheet.Cells[$"C{irecordProject }"].Value = item.EndDate.ToString("MM/yyyy") + " - " + item.StartDate.ToString("MM/yyyy");
worksheet.Cells[$"E{irecordProject }"].Value = item.Position;
worksheet.Cells[$"I{irecordProject }"].Value = item.Name;
worksheet.Cells[$"I{irecordProject + 1}"].Value = "Description: " + item.Description;
worksheet.Cells[$"I{irecordProject + 2}"].Value = "Responsibilities: " + item.Responsibilities;
worksheet.Cells[$"I{irecordProject + 3}"].Value = "TeamSize: " + item.TeamSize;
int lengthTemp = item.Technologies.Count;
for (int i = 0; i < lengthTemp; i++)
{
if (i == (lengthTemp - 1))
{
strBuilder.Append($"{item.Technologies[i].Name}");
break;
}
strBuilder.Append($"{item.Technologies[i].Name}, ");
}
worksheet.Cells[$"I{irecordProject + 4}"].Value = "Technologies used: " + strBuilder.ToString();
strBuilder.Clear();
irecordProject += 5;
irecordNumber++;
}
#endregion
#region Write Image to excel
int rowIndex = 2;
int colIndex = 13;
int Width = 149;
int Height = 225;
string pathDefaultImage = _webHostEnvironment.WebRootPath + _appSettings.ImagePath + "avatar-default.png";
string pathImage = _webHostEnvironment.WebRootPath + _appSettings.ImagePath + $"{profilePerson.Avatar}";
Image avatar;
if (!System.IO.File.Exists(pathImage))
{
avatar = Image.FromFile(pathDefaultImage);
}
else
{
avatar = Image.FromFile(pathImage);
}
Bitmap img = new Bitmap(avatar);
if (img.HorizontalResolution == 0 || img.VerticalResolution == 0)
{
img.SetResolution(96, 96);
}
OfficeOpenXml.Drawing.ExcelPicture pic = worksheet.Drawings.AddPicture("Sample", img);
pic.SetPosition(rowIndex, 0, colIndex, 0);
pic.SetSize(Width, Height);
#endregion
excelPkg.Save();
string p_strPath = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
if (System.IO.File.Exists(p_strPath))
{
System.IO.File.Delete(p_strPath);
}
// Create excel file on physical disk
FileStream objFileStrm = System.IO.File.Create(p_strPath);
objFileStrm.Close();
// Write content to excel file
System.IO.File.WriteAllBytes(p_strPath, excelPkg.GetAsByteArray());
}
#region Convert Excel to Pdf + download
string p_strPathExcel = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
string p_strPathPdf = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.pdf";
var pdflName = $"CV/CV_{profilePerson.FullName}_{profilePerson.StaffId}.pdf";
var excelName = $"CV/CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
string downloadUrl = string.Format($"{WebAPI.Helpers.HttpContext.Current.Request.Scheme}://{WebAPI.Helpers.HttpContext.Current.Request.Host.Value}/{excelName}");
if (type == "excel")
{
return(downloadUrl);
}
Workbook workBook = new Workbook();
workBook.LoadFromFile(p_strPathExcel, ExcelVersion.Version2010);
workBook.SaveToFile(p_strPathPdf, FileFormat.PDF);
downloadUrl = string.Format($"{WebAPI.Helpers.HttpContext.Current.Request.Scheme}://{WebAPI.Helpers.HttpContext.Current.Request.Host.Value}/{pdflName}");
return(downloadUrl);
#endregion
}
