/// <summary>
/// Generate header table score.
/// </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="summary">The evaluation summary.</param>
private void GenerateScoreHeader(IWorkbook workbook, ISheet sheet1, ref int rowIndex,
SummaryEvaluationViewModel summary)
{
this.GenerateSubHeaderTable(workbook, sheet1, ref rowIndex);
IRow subContentRow = sheet1.CreateRow(rowIndex);
int cellSubContentIndex = 3;
string[] subContent = new string[]
{
"คะแนนรวม",
summary.Total.ToString(),
"100",
summary.GradeName,
""
};
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 6, 7));
foreach (var content in subContent)
{
ExcelService.CreateContentCell(workbook, sheet1, subContentRow, cellSubContentIndex, content);
cellSubContentIndex++;
}
rowIndex += 1;
IRow topicUserContent = sheet1.CreateRow(rowIndex);
ExcelService.CreateContentCellNoBorder(workbook, sheet1, topicUserContent, 1, "คะแนนผู้ประเมิน");
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
rowIndex += 1;
}
/// <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 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 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 period 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 GeneratePeriodHeaderTable(IWorkbook workbook, ISheet sheet1,
int[] periodItemIds)
{
int startCellPeriod = 6;
int startRowPeriod = 5;
var periodItemList = _unitOfWork.GetRepository <PeriodItem>().GetCache(x => periodItemIds.Contains(x.Id),
orderBy: x => x.OrderBy(y => y.StartEvaDate));
foreach (var item in periodItemList)
{
ExcelService.SetCellHeaderStyle(workbook, sheet1.GetRow(startRowPeriod), startCellPeriod, startCellPeriod + 2);
ExcelService.SetCellHeaderStyle(workbook, sheet1.GetRow(startRowPeriod + 1), startCellPeriod, startCellPeriod + 2);
ExcelService.SetCellHeaderStyle(workbook, sheet1.GetRow(startRowPeriod + 2), startCellPeriod, startCellPeriod + 2);
ExcelService.CreateHeaderCell(workbook, sheet1, sheet1.GetRow(startRowPeriod), startCellPeriod, $"รอบการประเมิน {item.PeriodName}");
ExcelService.CreateHeaderCell(workbook, sheet1, sheet1.GetRow(startRowPeriod + 1), startCellPeriod,
this.GetPeriodDisplayTh(item.StartEvaDate.Value, item.EndEvaDate.Value));
ExcelService.CreateHeaderCell(workbook, sheet1, sheet1.GetRow(startRowPeriod + 2), startCellPeriod, "เกรด");
ExcelService.CreateHeaderCell(workbook, sheet1, sheet1.GetRow(startRowPeriod + 2), startCellPeriod + 1, "คะแนน");
ExcelService.CreateHeaderCell(workbook, sheet1, sheet1.GetRow(startRowPeriod + 2), startCellPeriod + 2, "เกณฑ์การประเมิน");
sheet1.AddMergedRegion(new CellRangeAddress(startRowPeriod, startRowPeriod, startCellPeriod, startCellPeriod + 2));
sheet1.AddMergedRegion(new CellRangeAddress(startRowPeriod + 1, startRowPeriod + 1, startCellPeriod, startCellPeriod + 2));
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,
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 topic report.
/// </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="model">The filter criteria value.</param>
private void GenerateTopicReport(IWorkbook workbook, ISheet sheet1, ref int rowIndex,
VendorEvaluationStatusReportRequestModel 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(3);
criteriaRow.Height = 3000;
ExcelService.CreateCriteriaCell(workbook, sheet1, criteriaRow, 0, $"{this.GenerateCriteria(model)}");
ExcelService.SetCellCriteriaStyle(workbook, criteriaRow, 1, 3);
rowIndex = rowIndex + 2;
}
/// <summary>
/// Generate evaluation compare report content.
/// </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="periodItemIds">The period item identitys.</param>
/// <param name="evaluationList">The evaluation collection.</param>
/// <param name="summaryList">The summary evaluation collection.</param>
private void GenerateContent(IWorkbook workbook, ISheet sheet1, int rowIndex, int[] periodItemIds,
IEnumerable <Data.Pocos.Evaluation> evaluationList,
IEnumerable <SummaryEvaluationViewModel> summaryList)
{
var groupVendor = evaluationList.Select(x => x.VendorNo).Distinct();
var vendorList = _unitOfWork.GetRepository <Data.Pocos.Vendor>().GetCache();
var valueHelpList = _unitOfWork.GetRepository <ValueHelp>().GetCache(x => x.ValueType == ConstantValue.ValueTypeWeightingKey);;
foreach (var item in groupVendor)
{
rowIndex++;
IRow contentRow = sheet1.CreateRow(rowIndex);
int cellMainContent = 0;
var vendor = vendorList.FirstOrDefault(x => x.VendorNo == item);
var evaluationGroup = evaluationList.Where(x => x.VendorNo == item);
var evaluationGroupDistinct = evaluationGroup.Select(x =>
new { x.WeightingKey, x.ComCode, x.PurchasingOrg }).Distinct();
ExcelService.CreateContentCell(workbook, sheet1, contentRow, cellMainContent++, item);
ExcelService.CreateContentCell(workbook, sheet1, contentRow, cellMainContent++, vendor?.VendorName);
bool isFirst = true;
foreach (var evaInfo in evaluationGroupDistinct)
{
int cellContent = cellMainContent;
var valueHelp = valueHelpList.FirstOrDefault(x => x.ValueKey == evaInfo.WeightingKey);
string[] mainContent = new string[]
{
evaInfo.WeightingKey,
valueHelp.ValueText,
evaInfo.ComCode,
evaInfo.PurchasingOrg
};
IRow contentRow2 = this.GenerateMainContent(workbook, sheet1, contentRow, mainContent,
ref isFirst, ref rowIndex, ref cellContent);
var tempEvaluationGroup = evaluationGroup.Where(x => x.WeightingKey == evaInfo.WeightingKey &&
x.ComCode == evaInfo.ComCode &&
x.PurchasingOrg == evaInfo.PurchasingOrg);
this.GeneratePeriodContent(workbook, sheet1, contentRow2, cellContent, periodItemIds,
tempEvaluationGroup, summaryList);
}
}
}
/// <summary>
/// Generate user cell content data.
/// </summary>
/// <param name="summary">The evaluation summary result.</param>
/// <param name="cellContentIndex">The target cell content index.</param>
/// <param name="workbook">The npoi workbook interface.</param>
/// <param name="sheet1">The npoi sheet interface.</param>
/// <param name="contentRow">The npoi content row interface.</param>
private void GenerateUserCellContent(SummaryEvaluationViewModel summary, ref int cellContentIndex,
IWorkbook workbook, ISheet sheet1, IRow contentRow)
{
foreach (var user in summary.UserLists)
{
string[] content = new string[]
{
user.FullName,
this.GetEvaStatus(user.IsAction, user.IsReject),
user.ReasonReject
};
foreach (var userContent in content)
{
ExcelService.CreateContentCell(workbook, sheet1, contentRow, cellContentIndex, userContent);
cellContentIndex++;
}
}
}
/// <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 sub header table in report.
/// </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 GenerateSubHeaderTable(IWorkbook workbook, ISheet sheet1,
ref int rowIndex)
{
string[] mainHeaders = new string[]
{
"คะแนนที่ได้",
"คะแนนเต็ม",
"เกณฑ์การประเมินที่ได้",
"เกณฑ์การประเมินที่ได้"
};
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 6, 7));
IRow headerRow = sheet1.CreateRow(rowIndex);
int cellHeaderIndex = 4;
foreach (var item in mainHeaders)
{
ExcelService.SetCellHeaderStyle(workbook, headerRow, cellHeaderIndex);
ExcelService.CreateHeaderCell(workbook, sheet1, headerRow, cellHeaderIndex, item);
cellHeaderIndex++;
}
rowIndex++;
}
/// <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);
}