/// <summary>
/// Generate header table.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="rowIndex">The row target index.</param>
private void GenerateHeaderTable(IWorkbook workbook, ISheet sheet1,
ref int rowIndex)
{
string[] mainHeaders = new string[]
{
"เลขที่ใบประเมิน",
"บริษัท",
"รอบการประเมิน",
"รอบการประเมิน",
"ชื่อรอบการประเมิน",
"รหัสผู้ขาย",
"ชื่อผู้ขาย",
"ชื่อประเภทผู้ขาย",
"ชื่อกลุ่มจัดซื้อ",
};
IRow headerRow = sheet1.CreateRow(rowIndex);
headerRow.Height = 500;
int cellHeaderIndex = 0;
foreach (var item in mainHeaders)
{
ExcelService.SetCellHeaderStyle(workbook, headerRow, cellHeaderIndex);
ExcelService.CreateHeaderCell(workbook, sheet1, headerRow, cellHeaderIndex, item);
cellHeaderIndex++;
}
rowIndex++;
IRow headerRow2 = sheet1.CreateRow(rowIndex);
ExcelService.SetCellContentStyle(workbook, headerRow2, 0, cellHeaderIndex - 1);
}
/// <summary>
/// Generate evaluation template and user score evaluation.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="rowIndex">The row target index.</param>
/// <param name="weigthingKey">The weighting key condition max score.</param>
/// <param name="summary">The evaluation summary.</param>
/// <param name="evaTemplate">The evaluation template.</param>
/// <param name="evaLogs">The user evaluation log collection.</param>
private void GenerateCriteriaContent(IWorkbook workbook, ISheet sheet1, ref int rowIndex,
string weigthingKey,
SummaryEvaluationViewModel summary,
EvaluationTemplateDisplayViewModel evaTemplate,
UserEvaluationDetailViewModel evaLogs)
{
foreach (var item in evaTemplate.Criteria.CriteriaGroups)
{
rowIndex++;
string criteriaGroup = $" {item.Sequence}. {item.KpiGroupNameTh}";
string score = this.GetScore(evaLogs, item.KpiGroupId, 0);
IRow kpiGroupContent = sheet1.CreateRow(rowIndex);
ExcelService.CreateContentCell(workbook, sheet1, kpiGroupContent, 2, criteriaGroup, horizontalAlignment: HorizontalAlignment.Left);
ExcelService.SetCellContentStyle(workbook, kpiGroupContent, 3, 3);
ExcelService.CreateContentCell(workbook, sheet1, kpiGroupContent, 4, score);
ExcelService.CreateContentCell(workbook, sheet1, kpiGroupContent, 5, this.GetMaxScore(weigthingKey, item.MaxScore));
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 2, 3));
foreach (var subItem in item.CriteriaItems)
{
rowIndex++;
string criteriaItem = $" {item.Sequence}.{subItem.Sequence}. {subItem.KpiNameTh}";
string subScore = this.GetScore(evaLogs, item.KpiGroupId, subItem.KpiId.Value);
IRow kpiContent = sheet1.CreateRow(rowIndex);
ExcelService.CreateContentCell(workbook, sheet1, kpiContent, 2, criteriaItem, horizontalAlignment: HorizontalAlignment.Left);
ExcelService.SetCellContentStyle(workbook, kpiContent, 3, 3);
ExcelService.CreateContentCell(workbook, sheet1, kpiContent, 4, score);
ExcelService.CreateContentCell(workbook, sheet1, kpiContent, 5, this.GetMaxScore(weigthingKey, subItem.MaxScore));
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 2, 3));
}
}
}
/// <summary>
/// Generate main content data.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="contentRow">The content last row.</param>
/// <param name="mainContent">The main content generate.</param>
/// <param name="isFirst">The vendor is first generate row or not.</param>
/// <param name="rowIndex">The row target index.</param>
/// <param name="cellContent">The cell content index.</param>
/// <returns></returns>
private IRow GenerateMainContent(IWorkbook workbook, ISheet sheet1, IRow contentRow,
string[] mainContent, ref bool isFirst,
ref int rowIndex, ref int cellContent)
{
IRow contentRow2 = contentRow;
if (isFirst)
{
foreach (var content in mainContent)
{
ExcelService.CreateContentCell(workbook, sheet1, contentRow2, cellContent++, content);
}
isFirst = false;
}
else
{
rowIndex++;
contentRow2 = sheet1.CreateRow(rowIndex);
ExcelService.SetCellContentStyle(workbook, contentRow2, 0, 1);
foreach (var content in mainContent)
{
ExcelService.CreateContentCell(workbook, sheet1, contentRow2, cellContent++, content);
}
}
return(contentRow2);
}
/// <summary>
/// Generate main content table.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="summaryList">The evaluation summary result. </param>
/// <param name="evaluationList">The evaluation data collection.</param>
/// <param name="rowIndex">The row data index.</param>
/// <param name="cellHeaderIndex">The max cell table generate.</param>
private void GenerateContentTable(IWorkbook workbook, ISheet sheet1,
SummaryEvaluationViewModel summary,
Data.Pocos.Evaluation evaluation,
ref int rowIndex,
PeriodItem periodItem, IEnumerable <Hremployee> empList)
{
rowIndex++;
IRow contentRow = sheet1.CreateRow(rowIndex);
int cellContentIndex = 0;
string[] mainContent = new string[]
{
evaluation.DocNo,
evaluation.ComCode,
UtilityService.DateTimeToString(periodItem.StartEvaDate.Value, "dd.MM.yyyy"),
UtilityService.DateTimeToString(periodItem.EndEvaDate.Value, "dd.MM.yyyy"),
periodItem.PeriodName,
evaluation.VendorNo,
summary.VendorName,
summary.WeightingKey,
summary.PurchasingOrgName,
};
foreach (var content in mainContent)
{
ExcelService.CreateContentCell(workbook, sheet1, contentRow, cellContentIndex, content);
cellContentIndex++;
}
rowIndex += 2;
var evaTemplate = _evaluationTemplateBll.LoadTemplate(evaluation.EvaluationTemplateId.Value);
this.GenerateScoreHeader(workbook, sheet1, ref rowIndex, summary);
foreach (var user in summary.UserLists)
{
var emp = empList.FirstOrDefault(x => x.Aduser == user.AdUser);
string evaluatorName = $" คุณ{emp?.FirstnameTh} {emp?.LastnameTh}";
IRow userContent = sheet1.CreateRow(rowIndex);
ExcelService.CreateContentCell(workbook, sheet1, userContent, 1, evaluatorName, horizontalAlignment: HorizontalAlignment.Left);
ExcelService.SetCellContentStyle(workbook, userContent, 2, 2);
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
var evaLogs = user.EvaluationLogs.FirstOrDefault();
this.GenerateCriteriaContent(workbook, sheet1, ref rowIndex, evaluation.WeightingKey, summary, evaTemplate, evaLogs);
rowIndex += 2;
}
}
/// <summary>
/// Generate period item content data.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="contentRow2">The content row target.</param>
/// <param name="startCellPeriod">The start cell for generate.</param>
/// <param name="periodItemIds">The all period item identity.</param>
/// <param name="tempEvaluationGroup">The evaluation conllection group.</param>
/// <param name="summaryList">The summary evaluation collection.</param>
private void GeneratePeriodContent(IWorkbook workbook, ISheet sheet1, IRow contentRow2,
int startCellPeriod, int[] periodItemIds,
IEnumerable <Data.Pocos.Evaluation> tempEvaluationGroup,
IEnumerable <SummaryEvaluationViewModel> summaryList)
{
foreach (var periodItemId in periodItemIds)
{
ExcelService.SetCellContentStyle(workbook, contentRow2, startCellPeriod, startCellPeriod + 2);
var evaluation = tempEvaluationGroup.FirstOrDefault(x => x.PeriodItemId == periodItemId);
if (evaluation != null)
{
var summary = summaryList.FirstOrDefault(x => x.Id == evaluation.Id);
ExcelService.CreateContentCell(workbook, sheet1, contentRow2, startCellPeriod, summary.GradeNameEn);
ExcelService.CreateContentCell(workbook, sheet1, contentRow2, startCellPeriod + 1, evaluation.TotalScore.Value.ToString());
ExcelService.CreateContentCell(workbook, sheet1, contentRow2, startCellPeriod + 2, summary.GradeName);
}
startCellPeriod = startCellPeriod + 3;
}
}
/// <summary>
/// Generate main content table.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="summaryList">The evaluation summary result. </param>
/// <param name="evaluationList">The evaluation data collection.</param>
/// <param name="rowIndex">The row data index.</param>
/// <param name="cellHeaderIndex">The max cell table generate.</param>
private void GenerateContentTable(IWorkbook workbook, ISheet sheet1,
IEnumerable <SummaryEvaluationViewModel> summaryList,
IEnumerable <Data.Pocos.Evaluation> evaluationList,
int rowIndex, int cellHeaderIndex)
{
var periodItemList = _unitOfWork.GetRepository <PeriodItem>().GetCache();
foreach (var item in evaluationList)
{
rowIndex++;
var periodItem = periodItemList.FirstOrDefault(x => x.Id == item.PeriodItemId.Value);
IRow contentRow = sheet1.CreateRow(rowIndex);
int cellContentIndex = 0;
var summary = summaryList.FirstOrDefault(x => x.Id == item.Id);
string[] mainContent = new string[]
{
item.DocNo,
item.ComCode,
UtilityService.DateTimeToString(periodItem.StartEvaDate.Value, "dd.MM.yyyy"),
UtilityService.DateTimeToString(periodItem.EndEvaDate.Value, "dd.MM.yyyy"),
item.VendorNo,
summary.VendorName,
summary.WeightingKey,
summary.PurchasingOrgName,
summary.UserLists.Count.ToString(),
summary.UserLists.Where(x => x.IsAction).Count().ToString(),
};
foreach (var content in mainContent)
{
ExcelService.CreateContentCell(workbook, sheet1, contentRow, cellContentIndex, content);
cellContentIndex++;
}
this.GenerateUserCellContent(summary, ref cellContentIndex, workbook, sheet1, contentRow);
if (cellContentIndex < cellHeaderIndex)
{
ExcelService.SetCellContentStyle(workbook, contentRow, cellContentIndex, cellHeaderIndex - 1);
}
}
}
/// <summary>
/// Generate header table.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="rowIndex">The row target index.</param>
/// <param name="maxCountUser">The max count user for generate dynamic header column.</param>
/// <param name="model">The filter criteria value.</param>
/// <returns></returns>
private int GenerateHeaderTable(IWorkbook workbook, ISheet sheet1,
ref int rowIndex, int maxCountUser,
InvestigateEvaluationReportRequestModel model)
{
IRow topicRow = sheet1.CreateRow(rowIndex);
ExcelService.CreateTopicCell(workbook, sheet1, topicRow, 0, $"รายงานตรวจสอบสถานะการประเมิน - วันที่พิมพ์ {UtilityService.DateTimeToStringTH(DateTime.Now, "dd MMM yyyy")}");
rowIndex = rowIndex + 1;
IRow criteriaRow = sheet1.CreateRow(rowIndex);
criteriaRow.Height = 3000;
ExcelService.CreateCriteriaCell(workbook, sheet1, criteriaRow, 0, $"{this.GenerateCriteria(model)}");
ExcelService.SetCellCriteriaStyle(workbook, criteriaRow, 1, 3);
rowIndex = rowIndex + 2;
string[] mainHeaders = new string[]
{
"เลขที่ใบประเมิน",
"บริษัท",
"รอบการประเมิน",
"รอบการประเมิน",
"รหัสผู้ขาย",
"ชื่อผู้ขาย",
"ชื่อประเภทผู้ขาย",
"ชื่อกลุ่มจัดซื้อ",
"จำนวนผู้ประเมิน",
"ประเมินแล้วเสร็จ",
};
IRow headerRow = sheet1.CreateRow(rowIndex);
headerRow.Height = 500;
int cellHeaderIndex = 0;
foreach (var item in mainHeaders)
{
ExcelService.SetCellHeaderStyle(workbook, headerRow, cellHeaderIndex);
ExcelService.CreateHeaderCell(workbook, sheet1, headerRow, cellHeaderIndex, item);
cellHeaderIndex++;
}
string[] userHeaders = new string[]
{
"ผู้ประเมิน",
"สถานะ",
"เหตุผล"
};
for (int i = 0; i < maxCountUser; i++)
{
foreach (var item in userHeaders)
{
ExcelService.SetCellHeaderStyle(workbook, headerRow, cellHeaderIndex);
ExcelService.CreateHeaderCell(workbook, sheet1, headerRow, cellHeaderIndex, item);
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, cellHeaderIndex, cellHeaderIndex));
cellHeaderIndex++;
}
}
rowIndex++;
IRow headerRow2 = sheet1.CreateRow(rowIndex);
ExcelService.SetCellContentStyle(workbook, headerRow2, 0, cellHeaderIndex - 1);
return(cellHeaderIndex);
}