public async Task <IActionResult> SearchExcel([FromBody] AuditRecSearch model)
{
var data = await _service.SearchExcel(model, "2");
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\WT_Summary_Report.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
//lưu data với ASPO vào excel
designer.SetDataSource("result", data);
designer.Process();
Worksheet ws = designer.Workbook.Worksheets[0];
for (var i = 1; i <= data.Count; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data[i - 1].Before_Picture;
var filePathAfter = "wwwroot\\uploaded\\images\\" + data[i - 1].After_Picture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 17, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
if (System.IO.File.Exists(filePathAfter))
{
//Add picture và set Size cho image
var pictureIndex = ws.Pictures.Add(i, 18, filePathAfter);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
if (data.Count != 0)
{
designer.Workbook.Save(stream, SaveFormat.Xlsx);
}
// designer.Workbook.Save (path + "Test.xlsx", SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WT_Summary_Report" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <List <AuditRecDto> > SearchExcel(AuditRecSearch model)
{
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditRecDto = await listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y) => new AuditRecDto
{
Record_ID = x.Record_ID,
Record_Time = y.Record_Time,
After_Picture = x.After_Picture,
Audit_Item = x.Audit_Item,
Audit_Type_ID = x.Audit_Type_ID,
Audit_Type = _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type2,
Before_Picture = x.Before_Picture,
Finished_Date = x.Finished_Date,
ERCS = x.ERCS,
Implement_Time = x.Implement_Time,
Implement_User = x.Implement_User,
Issue_EN = x.Issue_EN,
Issue_LL = x.Issue_LL,
Issue_ZW = x.Issue_ZW,
PDC = y.PDC,
Line = y.Line,
Building = y.Building,
ME_PIC = x.ME_PIC,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Chief = y.Chief,
Recorder = y.Recorder,
Attendees = y.Attendees,
PD_PIC = x.PD_PIC,
PD_RESP = x.PD_RESP,
Remark = x.Remark,
Status = x.Status,
Item_no = x.Item_no,
Updated_By = x.Updated_By,
Updated_Time = x.Updated_Time
}).ToListAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim() &&
x.Building.Trim() == model.Building.Trim() &&
x.Line.Trim() == model.Line.Trim() &&
x.PDC.Trim() == model.PDC.Trim() &&
x.Record_Time >= Convert.ToDateTime(model.From_Date + " 00:00") &&
x.Record_Time <= Convert.ToDateTime(model.To_Date + " 00:00")).ToList();
if (model.Model_No != "all")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim()).ToList();
}
if (model.Model_Name != "" && model.Model_Name != string.Empty && model.Model_Name != null)
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Contains(model.Model_Name)).ToList();
}
if (model.Audit_Type_1 != "all")
{
var auditTypeMFind = await _repoAuditTypeM.FindAll().Where(x => x.Audit_Type1.Trim() == model.Audit_Type_1 &&
x.Audit_Type2.Trim() == model.Audit_Type_2).FirstOrDefaultAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMFind.Audit_Type_ID).ToList();
}
return(listAuditRecDto);
}
public async Task <PagedList <AuditRecDto> > SearchByModel(PaginationParams param, AuditRecSearch model)
{
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditMes = _mesAuditOrg.FindAll().Where(x => x.Status == 1);
var listAuditRecDto = listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y) => new { x, y }).Join(listAuditMes, z => z.y.Line, t => t.Line_ID_2, (z, t) => new AuditRecDto
{
Record_ID = z.x.Record_ID,
Record_Time = z.y.Record_Time,
After_Picture = z.x.After_Picture,
Audit_Item = z.x.Audit_Item,
Audit_Type_ID = z.x.Audit_Type_ID,
Audit_Type = z.x.Audit_Type_ID == null || z.x.Audit_Type_ID == "" ? "" : _repoAuditTypeM.FindById(z.x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(z.x.Audit_Type_ID).Audit_Type2,
Before_Picture = z.x.Before_Picture,
Finished_Date = z.x.Finished_Date,
ERCS = z.x.ERCS,
Implement_Time = z.x.Implement_Time,
Implement_User = z.x.Implement_User,
Issue_EN = z.x.Issue_EN,
Issue_LL = z.x.Issue_LL,
Issue_ZW = z.x.Issue_ZW,
Building = z.y.Building,
PDC = z.y.PDC,
Line = z.y.Line,
Line_Name = t.Line_ID_2_Name,
ME_PIC = z.x.ME_PIC,
Model_Name = z.y.Model_Name,
Model_No = z.y.Model_No,
Chief = z.y.Chief,
Recorder = z.y.Recorder,
Attendees = z.y.Attendees,
PD_PIC = z.x.PD_PIC,
PD_Department = z.x.PD_Department,
PD_Building = z.x.PD_Building,
Remark = z.x.Remark,
Status = z.x.Status,
Item_no = z.x.Item_no,
Updated_By = z.x.Updated_By,
Updated_Time = z.x.Updated_Time
}).ToList();
if (model.Status != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim()).ToList();
}
if (model.Building != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Building.Trim() == model.Building.Trim()).ToList();
}
if (model.Line != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Line.Trim() == model.Line.Trim()).ToList();
}
if (model.PDC != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.PDC.Trim() == model.PDC.Trim()).ToList();
}
if (model.Model_No != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim()).ToList();
}
if (model.Model_Name != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Trim() == model.Model_Name.Trim()).ToList();
}
if (model.Audit_Type_1 != "")
{
var auditTypeMFind = await _repoAuditTypeM.FindAll(x => x.Audit_Type1.Trim() == model.Audit_Type_1.Trim() && x.Audit_Type2.Trim() == model.Audit_Type_2.Trim()).FirstOrDefaultAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMFind.Audit_Type_ID).ToList();
}
listAuditRecDto = listAuditRecDto.Select(
x =>
{
x.ME_PIC_Name = GetMePicByID(x.ME_PIC);
x.PD_PIC = GetPdPicByID(x.PD_PIC);
return(x);
}).OrderByDescending(x => x.Record_Time).ThenBy(x => x.Item_no).ToList();
return(PagedList <AuditRecDto> .Create(listAuditRecDto, param.PageNumber, param.PageSize));
}
public async Task <List <AuditRecDto> > SearchExcel(AuditRecSearch model, string WT = "1")
{
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditRecDto = await listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y) => new AuditRecDto
{
Record_ID = x.Record_ID,
Record_Time = y.Record_Time,
After_Picture = x.After_Picture,
Audit_Item = x.Audit_Item,
Audit_Type_ID = x.Audit_Type_ID,
Audit_Type = x.Audit_Type_ID == null || x.Audit_Type_ID == "" ? "" : _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type2,
Before_Picture = x.Before_Picture,
Finished_Date = x.Finished_Date,
ERCS = x.ERCS,
Implement_Time = x.Implement_Time,
Implement_User = x.Implement_User,
Issue_EN = x.Issue_EN,
Issue_LL = x.Issue_LL,
Issue_ZW = x.Issue_ZW,
PDC = y.PDC,
Line = y.Line,
Building = y.Building,
ME_PIC = x.ME_PIC,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Chief = y.Chief,
Recorder = y.Recorder,
Attendees = y.Attendees,
PD_PIC = x.PD_PIC,
PD_Department = x.PD_Department,
PD_Building = x.PD_Building,
Remark = x.Remark,
Item_no = x.Item_no,
Status = x.Status,
Updated_By = x.Updated_By,
Updated_Time = x.Updated_Time
}).ToListAsync();
if (WT != "1")
{
listAuditRecDto = listAuditRecDto.Select(
x =>
{
x.ME_PIC = GetMePicByID(x.ME_PIC);
x.PD_PIC = GetPdPicByID(x.PD_PIC);
return(x);
}).ToList();
}
if (model.Status != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim()).ToList();
}
if (model.PDC != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.PDC.Trim() == model.PDC.Trim()).ToList();
}
if (model.Building != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Building.Trim() == model.Building.Trim()).ToList();
}
if (model.Line != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Line.Trim() == model.Line.Trim()).ToList();
}
if (model.Model_Name != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Trim() == model.Model_Name.Trim()).ToList();
}
if (model.Model_No != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim()).ToList();
}
if (model.Audit_Type_1 != "")
{
var auditTypeMFind = await _repoAuditTypeM.FindAll().Where(x => x.Audit_Type1.Trim() == model.Audit_Type_1.Trim() && x.Audit_Type2.Trim() == model.Audit_Type_2.Trim()).FirstOrDefaultAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMFind.Audit_Type_ID).ToList();
}
return(listAuditRecDto);
}
public async Task <IActionResult> SearchByModel([FromQuery] PaginationParams param, [FromBody] AuditRecSearch model)
{
var auditRecs = await _service.SearchByModel(param, model);
Response.AddPagination(auditRecs.CurrentPage, auditRecs.PageSize, auditRecs.TotalCount, auditRecs.TotalPages);
return(Ok(auditRecs));
}
public async Task <IActionResult> ExportExcelWTTrackingList([FromBody] AuditRecSearch model)
{
var data = await _service.SearchExcel(model, "2");
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\WT_Tracking_List.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
//gán giá trị tĩnh
Worksheet ws = designer.Workbook.Worksheets[0];
if (data.Count != 0)
{
ws.Cells["B2"].PutValue(data[0].Record_Time);
ws.Cells["B3"].PutValue(data[0].PDC);
ws.Cells["B4"].PutValue(data[0].Attendees);
ws.Cells["D2"].PutValue(data[0].Building);
ws.Cells["D3"].PutValue(data[0].Line);
ws.Cells["F2"].PutValue(data[0].Model_Name);
ws.Cells["F3"].PutValue(data[0].Model_No);
ws.Cells["I2"].PutValue(data[0].Chief);
ws.Cells["I3"].PutValue(data[0].Recorder);
}
//lưu data với ASPO vào excel
designer.SetDataSource("result", data);
designer.Process();
//Chèn image vào excel và set lại rows
for (var i = 6; i <= data.Count + 5; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data[i - 6].Before_Picture;
var filePathAfter = "wwwroot\\uploaded\\images\\" + data[i - 6].After_Picture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 7, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
if (System.IO.File.Exists(filePathAfter))
{
//Add picture và set Size cho image
var pictureIndex = ws.Pictures.Add(i, 8, filePathAfter);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
// designer.Workbook.Save (path + "Test.xlsx", SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WT_Tracking_List" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <IActionResult> GetSearchExcel([FromBody] AuditRecSearch model)
{
var auditRecs = await _service.SearchExcel(model);
return(Ok(auditRecs));
}
public async Task <IActionResult> SearchExcel([FromBody] AuditRecSearch model)
{
var auditRecs = await _iWTTrackingReportService.SearchExcel(model);
return(Ok(auditRecs));
}
public async Task <List <AuditRecDto> > SearchExcel(AuditRecSearch model)
{
var auditTypeMs = await _repoAuditTypeM.GetAll().ToListAsync();
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditOrg = _mesAuditOrg.FindAll().Where(x => x.Status == 1);
var listAuditRecDto = await listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y)
=> new AuditRecDto
{
Record_ID = x.Record_ID,
Record_Time = y.Record_Time,
After_Picture = x.After_Picture,
Audit_Item = x.Audit_Item,
Audit_Type_ID = x.Audit_Type_ID,
Audit_Type = x.Audit_Type_ID == null || x.Audit_Type_ID == "" ? "" : _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type2,
Before_Picture = x.Before_Picture,
Finished_Date = x.Finished_Date,
ERCS = x.ERCS,
Implement_Time = x.Implement_Time,
Implement_User = x.Implement_User,
Issue_EN = x.Issue_EN,
Issue_LL = x.Issue_LL,
Issue_ZW = x.Issue_ZW,
PDC = y.PDC,
PDC_Name = listAuditOrg.Where(t => t.PDC_ID == y.PDC).FirstOrDefault().PDC_Name,
Line = y.Line,
Building = y.Building,
ME_PIC = x.ME_PIC,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Chief = y.Chief,
Recorder = y.Recorder,
Attendees = y.Attendees,
PD_PIC = x.PD_PIC,
PD_Department = x.PD_Department,
PD_Building = x.PD_Building,
Remark = x.Remark,
Status = x.Status,
Item_no = x.Item_no,
Updated_By = x.Updated_By,
Updated_Time = x.Updated_Time
}).ToListAsync();
listAuditRecDto = listAuditRecDto.Select(x => {
x.ME_PIC = GetMePicByID(x.ME_PIC);
x.PD_PIC = GetPdPicByID(x.PD_PIC);
return(x);
}).ToList();
if (model.Status != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim()).ToList();
}
if (model.Line != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Line.Trim() == model.Line.Trim()).ToList();
}
if (model.From_Date != "" && model.To_Date != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Record_Time >= Convert.ToDateTime(model.From_Date + " 00:00") &&
x.Record_Time <= Convert.ToDateTime(model.To_Date + " 00:00")).ToList();
}
if (model.Model_No != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim()).ToList();
}
if (model.Model_Name != "" && model.Model_Name != string.Empty && model.Model_Name != null)
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Contains(model.Model_Name)).ToList();
}
if (model.Audit_Type_1 != "")
{
// Lấy AuditTypeM Có Version lớn nhất
var auditTypeM = auditTypeMs.Where(x => x.Audit_Type_ID.Trim() == model.Audit_Type_1.Trim()).FirstOrDefault();
var auditTypeMVersion = auditTypeMs.Where(x => x.Audit_Kind == auditTypeM.Audit_Kind &&
x.Audit_Type1 == auditTypeM.Audit_Type1 &&
x.Audit_Type2 == auditTypeM.Audit_Type2)
.OrderByDescending(x => x.Version).FirstOrDefault();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMVersion.Audit_Type_ID).ToList();
}
return(listAuditRecDto);
}