/// <summary> /// Export vendor evaluation status excel report. /// </summary> /// <param name="model">The filter criteria value.</param> public ResponseFileModel ExportVendorEvaluationStatusReport(VendorEvaluationStatusReportRequestModel model) { var dataList = this.GetDataCollection(model); var summaryList = this.GetSummarys(dataList); return(this.ExportExcel(dataList, summaryList, model)); }
public IActionResult ExportSummaryReport([FromBody] VendorEvaluationStatusReportRequestModel model) { var result = _vendorEvaluationStatusReport.ExportVendorEvaluationStatusReport(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, 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> /// 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, VendorEvaluationStatusReportRequestModel 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)); var periodItemList = _unitOfWork.GetRepository <PeriodItem>().GetCache(); var empList = _unitOfWork.GetRepository <Hremployee>().GetCache(); int rowIndex = 2; this.GenerateTopicReport(workbook, sheet1, ref rowIndex, model); foreach (var item in evaluationList) { var summary = summaryList.FirstOrDefault(x => x.Id == item.Id); var periodItem = periodItemList.FirstOrDefault(x => x.Id == item.PeriodItemId.Value); this.GenerateHeaderTable(workbook, sheet1, ref rowIndex); //Merge header table. sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 0, 0)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 1, 1)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 2, 3)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 4, 4)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 5, 5)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 6, 6)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 7, 7)); sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, 8, 8)); this.GenerateContentTable(workbook, sheet1, summary, item, ref rowIndex, periodItem, empList); rowIndex++; } workbook.Write(memoryStream); result.FileContent = memoryStream.ToArray(); result.FileName = $"EvaluationVendorStatus_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; } return(result); }
/// <summary> /// Generate criteria selected information in report. /// </summary> /// <param name="model">The filter criteria value.</param> /// <returns></returns> private string GenerateCriteria(VendorEvaluationStatusReportRequestModel 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 evaluation data. /// </summary> /// <param name="model">The filter criteria value.</param> /// <returns></returns> private IEnumerable <Data.Pocos.Evaluation> GetDataCollection(VendorEvaluationStatusReportRequestModel 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(VendorEvaluationStatusReportRequestModel model) { // simple method to dynamically plugin a where clause var predicate = PredicateBuilder.True <Data.Pocos.Evaluation>(); // true -where(true) return all 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); }