/// <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,
EvaluationCompareReportRequestModel model)
{
var result = new ResponseFileModel();
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));
int[] periodItemIds = this.GetPeriodByCriteria(model);
int rowIndex = 2;
this.GenerateTopicReport(workbook, sheet1, ref rowIndex, model);
this.GenerateHeaderTable(workbook, sheet1, ref rowIndex);
this.GeneratePeriodHeaderTable(workbook, sheet1, periodItemIds);
this.GenerateContent(workbook, sheet1, rowIndex, periodItemIds, evaluationList, summaryList);
workbook.Write(memoryStream);
result.FileContent = memoryStream.ToArray();
result.FileName = $"EvaluationCompare_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
}
return(result);
}
/// <summary>
/// Export vendor evaluation status excel report.
/// </summary>
/// <param name="model">The filter criteria value.</param>
public ResponseFileModel ExportEvaluationCompareReport(EvaluationCompareReportRequestModel model)
{
var dataList = this.GetDataCollection(model);
var summaryList = this.GetSummarys(dataList);
return(this.ExportExcel(dataList, summaryList, model));
}
public IActionResult ExportSummaryReport([FromBody] EvaluationCompareReportRequestModel model)
{
var result = _evaluationCompareReport.ExportEvaluationCompareReport(model);
Response.Headers.Add("Content-Disposition", "attachment; filename=" + result.FileName);
return(File(result.FileContent, "application/octet-stream"));
}
/// <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,
EvaluationCompareReportRequestModel 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 criteria selected information in report.
/// </summary>
/// <param name="model">The filter criteria value.</param>
/// <returns></returns>
private string GenerateCriteria(EvaluationCompareReportRequestModel 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>
/// Get period by criteria selected.
/// </summary>
/// <param name="model">The criteria value.</param>
/// <returns></returns>
private int[] GetPeriodByCriteria(EvaluationCompareReportRequestModel model)
{
List <int> result = new List <int>();
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),
orderBy: x => x.OrderBy(y => y.StartEvaDate)).Select(x => x.Id);
if (model.PeriodItemId != null)
{
periodItemIds = periodItemIds.Where(x => model.PeriodItemId.Contains(x));
}
result.AddRange(periodItemIds);
}
else
{
result.AddRange(_unitOfWork.GetRepository <PeriodItem>().GetCache(orderBy: x => x.OrderBy(y => y.StartEvaDate)).Select(x => x.Id));
}
return(result.ToArray());
}
/// <summary>
/// Get evaluation data.
/// </summary>
/// <param name="model">The filter criteria value.</param>
/// <returns></returns>
private IEnumerable <Data.Pocos.Evaluation> GetDataCollection(EvaluationCompareReportRequestModel 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(EvaluationCompareReportRequestModel model)
{
// simple method to dynamically plugin a where clause
var predicate = PredicateBuilder.True <Data.Pocos.Evaluation>(); // true -where(true) return all
//filter status
predicate = predicate.And(s => s.Status == ConstantValue.WorkflowStatusApproved);
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);
}
