/// <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 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 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); } } }