/// <summary>
/// Export summary evaluation excel report.
/// </summary>
/// <param name="model">The filter criteria value.</param>
public ResponseFileModel ExportSummaryReport(InvestigateEvaluationReportRequestModel model)
{
var dataList = this.GetDataCollection(model);
var summaryList = this.GetSummarys(dataList);
return(this.ExportExcel(dataList, summaryList, model));
}
public IActionResult ExportSummaryReport([FromBody] InvestigateEvaluationReportRequestModel model)
{
var result = _evaluationSummaryReport.ExportSummaryReport(model);
Response.Headers.Add("Content-Disposition", "attachment; filename=" + result.FileName);
return(File(result.FileContent, "application/octet-stream"));
}
/// <summary>
/// Generate criteria selected information in report.
/// </summary>
/// <param name="model">The filter criteria value.</param>
/// <returns></returns>
private string GenerateCriteria(InvestigateEvaluationReportRequestModel model)
{
StringBuilder stringBuilder = new StringBuilder();
string year = string.Empty;
if (model.Year != null)
{
year = string.Join(",", model.Year);
}
stringBuilder.AppendLine($" Criteria ที่เลือก");
stringBuilder.AppendLine($"ปี : {year}");
stringBuilder.AppendLine($"รอบ : {this.GetPeriodItemName(model.PeriodItemId)}");
stringBuilder.AppendLine($"บริษัท : {this.GetCompanyName(model.ComCode)}");
stringBuilder.AppendLine($"กลุ่มจัดซื้อ : {this.GetPurchaseName(model.PurchaseOrg)}");
stringBuilder.AppendLine($"ประเภทผู้ขาย : {this.GetWeightingKey(model.WeightingKey)}");
return(stringBuilder.ToString());
}
/// <summary>
/// Export evaluation summary stat to excel report.
/// </summary>
/// <param name="evaluationList">The evaluation collection.</param>
/// <param name="summaryList">The summary evaluation collection.</param>
/// <param name="model">The filter criteria value.</param>
private ResponseFileModel ExportExcel(IEnumerable <Data.Pocos.Evaluation> evaluationList,
IEnumerable <SummaryEvaluationViewModel> summaryList,
InvestigateEvaluationReportRequestModel model)
{
var result = new ResponseFileModel();
int maxCountUser = summaryList.Select(x => x.UserLists.Count).Max();
string sheetName = "รายงานตรวจสอบสถานะการประเมิน";
using (var memoryStream = new MemoryStream())
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet(sheetName);
sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 0, 7));
sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 3));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 0, 0));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 1, 1));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 2, 3));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 4, 4));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 5, 5));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 6, 6));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 7, 7));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 8, 8));
sheet1.AddMergedRegion(new CellRangeAddress(5, 6, 9, 9));
int rowIndex = 2;
int cellHeaderIndex = this.GenerateHeaderTable(workbook, sheet1, ref rowIndex, maxCountUser, model);
this.GenerateContentTable(workbook, sheet1, summaryList, evaluationList, rowIndex, cellHeaderIndex);
workbook.Write(memoryStream);
result.FileContent = memoryStream.ToArray();
result.FileName = $"InvestigateEvaluation_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
}
return(result);
}
/// <summary>
/// Get evaluation data.
/// </summary>
/// <param name="model">The filter criteria value.</param>
/// <returns></returns>
private IEnumerable <Data.Pocos.Evaluation> GetDataCollection(InvestigateEvaluationReportRequestModel model)
{
var whereClause = this.BuildDynamicWhereClause(model);
return(_unitOfWork.GetRepository <Data.Pocos.Evaluation>().Get(whereClause));
}
/// <summary>
/// Build dynamic where clause with criteria value.
/// </summary>
/// <param name="model">The criteria value.</param>
/// <returns></returns>
private Expression <Func <Data.Pocos.Evaluation, bool> > BuildDynamicWhereClause(InvestigateEvaluationReportRequestModel model)
{
// simple method to dynamically plugin a where clause
var predicate = PredicateBuilder.True <Data.Pocos.Evaluation>(); // true -where(true) return all
if (model.Status != null)
{
predicate = predicate.And(s => model.Status.Contains(s.Status));
}
if (!string.IsNullOrEmpty(model.ComCode))
{
predicate = predicate.And(s => s.ComCode == model.ComCode);
}
if (!string.IsNullOrEmpty(model.PurchaseOrg))
{
predicate = predicate.And(s => s.PurchasingOrg == model.PurchaseOrg);
}
if (!string.IsNullOrEmpty(model.WeightingKey))
{
predicate = predicate.And(s => s.WeightingKey == model.WeightingKey);
}
if (model.Year != null)
{
var periodAll = _unitOfWork.GetRepository <Period>().GetCache(x => model.Year.Contains(x.Year)).Select(x => x.Id);
var periodItemIds = _unitOfWork.GetRepository <PeriodItem>().GetCache(x => periodAll.Contains(x.PeriodId.Value)).Select(x => x.Id);
if (model.PeriodItemId != null)
{
periodItemIds = periodItemIds.Where(x => model.PeriodItemId.Contains(x));
}
predicate = predicate.And(s => periodItemIds.Contains(s.PeriodItemId.Value));
}
return(predicate);
}
/// <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);
}
