public async Task <ActionResult> ExportExcelSixsRecord([FromQuery] PaginationParams paginationParams, ScoreRecordParam sixsScoreRecordParam)
{
var data = await _iSixsReportService.GetListSixsScoreRecord(paginationParams, sixsScoreRecordParam, false);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Sixs_Score_Record_Template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
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", "Sixs_Score_Record" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <PagedList <SixsScoreRecordDto> > GetListSixsScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true)
{
// 6S là giá trị fix cứng
var paramAuditTypeBySixs = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "6S").Select(x => x.Audit_Type_ID).ToListAsync();
var queryAuditRateM = _auditRateMRepository.FindAll(x => paramAuditTypeBySixs.Contains(x.Audit_Type_ID));
var queryAuditRateD = _auditRateDRepository.FindAll();
var listAuditMes = _mesAuditOrg.FindAll(x => x.Status == 1);
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.AuditType1 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type1.Trim() == scoreRecordParam.AuditType1);
}
if (scoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == scoreRecordParam.AuditType2);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.Join(listAuditMes, x => x.Line, t => t.Line_ID_2, (x, t)
=> new SixsScoreRecordDto
{
RecordId = x.Record_ID,
AuditDate = x.Record_Date,
AuditType = x.Audit_Type1,
AuditType2 = x.Audit_Type2,
LineId = x.Line,
Line_Name = t.Line_ID_2_Name,
UpdateBy = x.Updated_By,
UpdateTime = x.Updated_Time,
Rating0 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_0),
Rating1 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_1),
RatingNa = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA) == null ? 0 : queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA),
CheckAnswerAllYet = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID && y.Rate_NA == 0 && y.Rating_0 == 0 && y.Rating_1 == 0 && y.Rating_2 == 0).Count() > 0 ? false : true
}).Distinct().OrderByDescending(x => x.UpdateTime);
return(await PagedList <SixsScoreRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <IActionResult> GetListSixsScoreRecord([FromQuery] PaginationParams paginationParams, ScoreRecordParam scoreRecordParam)
{
var data = await _iSixsReportService.GetListSixsScoreRecord(paginationParams, scoreRecordParam);
Response.AddPagination(data.CurrentPage, data.PageSize, data.TotalCount, data.TotalPages);
return(Ok(data));
}
public async Task <PagedList <SMEScoreRecordDto> > GetLisSMEScoreRecord(PaginationParams paginationParams, ScoreRecordParam sixsScoreRecordParam, bool isPaging = true)
{
var queryAuditRateM = _auditRateMRepository.FindAll().Where(x => x.Audit_Type1.Trim() == "SME2.0");
var queryAuditRateD = _auditRateDRepository.FindAll();
if (sixsScoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == sixsScoreRecordParam.PDC);
}
if (sixsScoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == sixsScoreRecordParam.Building);
}
if (sixsScoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == sixsScoreRecordParam.Line);
}
if (sixsScoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == sixsScoreRecordParam.AuditType2);
}
if (sixsScoreRecordParam.FromDate != "" && sixsScoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(sixsScoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(sixsScoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.OrderByDescending(x => x.Updated_Time).Select(x => new SMEScoreRecordDto
{
RecordId = x.Record_ID,
AuditDate = x.Record_Date,
AuditType = x.Audit_Type1,
AuditType2 = x.Audit_Type2,
LineId = x.PDC + " + " + x.Building + " + " + x.Line,
UpdateBy = x.Updated_By,
UpdateTime = x.Updated_Time,
Rating0 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_0),
Rating1 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_1),
Rating2 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_2),
RatingNa = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA) == null ? 0 : queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA),
});
return(await PagedList <SMEScoreRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <PagedList <WaterSpiderRecordDto> > GetLisWaterSpiderScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true)
{
// WS la gia tri fix cung
var paramAuditTypeIdByWaterSpider = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "WS").Select(x => x.Audit_Type_ID).ToListAsync();
var queryAuditRateM = _auditRateMRepository.FindAll(x => paramAuditTypeIdByWaterSpider.Contains(x.Audit_Type_ID));
var queryAuditRateD = _auditRateDRepository.FindAll();
var listAuditMes = _mesAuditOrgRepository.FindAll(x => x.Status == 1);
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.AuditType1 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type1.Trim() == scoreRecordParam.AuditType1);
}
if (scoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == scoreRecordParam.AuditType2);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.Join(listAuditMes, x => x.Line, t => t.Line_ID_2, (x, t) =>
new WaterSpiderRecordDto
{
RecordId = x.Record_ID,
AuditType = x.Audit_Type1,
AuditDate = x.Record_Date,
LineId = x.Line,
Line_Name = t.Line_ID_2_Name,
Score = _auditRateDRepository.SumEachRating1InAuditTypeDAndAuditRateD(x.Record_ID),
Loss = _auditRateDRepository.SumEachRating1InAuditTypeDAndAuditRateD(x.Record_ID),
NA = _auditRateDRepository.SumEachRatingNaInAuditTypeDAndAuditRateD(x.Record_ID),
CheckAnswerAllYet = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID && y.Rate_NA == 0 && y.Rating_2 == 0).Count() > 0 ? false : true
}).OrderByDescending(x => x.AuditDate);
return(await PagedList <WaterSpiderRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <PagedList <WaterSpiderScoreRecordDto> > GetLisWaterSpiderScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true)
{
var queryAuditRateM = _auditRateMRepository.FindAll().Where(x => x.Audit_Type1.Trim() == "精實系統/WS");
var queryAuditRateD = _auditRateDRepository.FindAll();
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.OrderByDescending(x => x.Updated_Time).Select(x => new WaterSpiderScoreRecordDto
{
RecordId = x.Record_ID,
AuditType = x.Audit_Type1,
AuditDate = x.Record_Date,
LineId = x.PDC + " + " + x.Building + " + " + x.Line,
Score = _auditRateDRepository.SumEachRating1InAuditTypeDAndAuditRateD(x.Record_ID)
});
return(await PagedList <WaterSpiderScoreRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <object> GetDataChart(string id, int type)
{
PaginationParams paginationParams = new PaginationParams();
ScoreRecordParam scoreRecordParam = new ScoreRecordParam();
DateTime today = DateTime.Now;
var firstDayLastOfMonth = new DateTime(today.Year, today.AddMonths(-1).Month, 1);
if (today.Month == 1)
{
firstDayLastOfMonth = new DateTime(today.Year - 1, today.AddMonths(-1).Month, 1);
}
var lastDayLastOfMonth = firstDayLastOfMonth.AddMonths(1).AddDays(-1);
scoreRecordParam.FromDate = firstDayLastOfMonth.ToString("yyyy/MM/dd");
scoreRecordParam.ToDate = lastDayLastOfMonth.ToString("yyyy/MM/dd");
//Query data by Factory
var queryLables = _iMesAuditrepo.FindAll().Where(x => x.Status == 1 && x.Line_Seq > 0);
var queryData = await _iSMEReocrdService.GetLisSMEScoreRecord(paginationParams, scoreRecordParam, false, true);
//GetLables chart by Factory
var listLables = await queryLables.Where(x => x.PDC_ID != "6" && x.PDC_ID != "8" && x.PDC_ID != "C" &&
x.PDC_ID != "D" && x.PDC_ID != "G").Select(x => x.PDC_ID).Distinct().ToListAsync();
listLables.Insert(0, "Overall");
//get name pdc
var listLables_name = await queryLables.Where(x => x.PDC_ID != "6" && x.PDC_ID != "8" && x.PDC_ID != "C" &&
x.PDC_ID != "D" && x.PDC_ID != "G").OrderBy(x => x.PDC_ID).Select(x => x.PDC_Name).ToListAsync();
listLables_name = listLables_name.Distinct().ToList();
listLables_name.Insert(0, "Overall");
//Get Data Chart
var dataChart = queryData.ToList();
// Get table weakness
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
var auditrateM = _rateMRepository.FindAll().Where(x => (x.Audit_Type2 == "Module1" || x.Audit_Type2 == "Module3") &&
x.PDC != "6" && x.PDC != "8" && x.PDC != "C" && x.PDC != "D" && x.PDC != "G" && x.Audit_Kind == "SME" &&
x.Record_Date >= d1 && x.Record_Date <= d2);
var auditrateD = _rateDRepository.FindAll().Where(x => x.Rating_0 == 1);
var AuditTypeD = _typeDRepository.FindAll();
var Title = "";
//HCE
var queryJoinRateMD = auditrateM.Join(auditrateD, x => x.Record_ID, y => y.Record_ID, (x, y) => new
{
x.Audit_Kind,
x.Audit_Type2,
x.Audit_Type_ID,
x.PDC,
x.Building,
x.Line,
y.Audit_Item_ID
});
var dataJoin = queryJoinRateMD.GroupJoin(AuditTypeD, x => new { x.Audit_Type_ID, x.Audit_Item_ID }, z => new { z.Audit_Type_ID, z.Audit_Item_ID }, (x, z) => new
{
AuditRatMD = x,
TypeD = z
})
.SelectMany(y => y.TypeD.DefaultIfEmpty(),
(y, z) => new
{
AuditRatMD = y.AuditRatMD,
TypeD = z
}).ToList();
var resultTest = dataJoin.GroupBy(x => x).Select(z => new
{
AudiKind = z.Key.AuditRatMD.Audit_Kind,
Audit_Type2 = z.Key.AuditRatMD.Audit_Type2,
PDC = z.Key.AuditRatMD.PDC,
Building = z.Key.AuditRatMD.Building,
Line = z.Key.AuditRatMD.Line,
Audit_Item_ID = z.Key.AuditRatMD.Audit_Item_ID,
Audit_Item_ZW = z.Key.TypeD.Audit_Item_ZW,
Audit_Item_EN = z.Key.TypeD.Audit_Item_EN,
Audit_Item_LL = z.Key.TypeD.Audit_Item_LL,
cnt = z.Count(),
Weakness = "Factory"
}).OrderBy(z => z.Audit_Type2).ToList();
//End HCE
//line_seq >0
var listLine = queryLables.Select(x => x.Line_ID).ToList();
resultTest = resultTest.Where(x => listLine.Contains(x.Line)).ToList();
//Get data chart & lables by DEPT
if (type == 1)
{
var Dept_name = queryLables.Where(x => x.PDC_ID == id).Select(x => x.PDC_Name).FirstOrDefault();
dataChart = queryData.Where(x => x.PDC == id).ToList();
listLables = await queryLables.Where(x => x.PDC_ID == id).Select(x => x.Building).Distinct().ToListAsync();
//get name building
listLables_name = await queryLables.Where(x => x.PDC_ID == id).OrderBy(x => x.Building).Select(x => x.Building_Name).ToListAsync();
listLables_name = listLables_name.Distinct().ToList();
listLables.Insert(0, "Dept " + id);
listLables_name.Insert(0, "Dept " + Dept_name);
resultTest = resultTest.Where(x => x.PDC.Trim() == id.Trim()).ToList();
Title = "Dept " + Dept_name;
}
//Get data chart & lables by Building
if (type == 2)
{
var Building_Name = queryLables.Where(x => x.Building == id).Select(x => x.Building_Name).FirstOrDefault();
dataChart = queryData.Where(x => x.Building == id).ToList();
var temp = queryLables.Where(x => x.Building == id && x.IsSME == true).Select(x => new { Line_ID = x.Line_ID, Line_Seq = x.Line_Seq }).Distinct();
listLables = await temp.OrderBy(x => x.Line_Seq).Select(x => x.Line_ID).ToListAsync();
//get Name Line
listLables_name = await queryLables.Where(x => x.Building == id && x.IsSME == true).OrderBy(x => x.Line_Seq).Select(x => x.Line_Name.Trim()).ToListAsync();
listLables_name = listLables_name.Distinct().ToList();
listLables.Insert(0, Building_Name);
listLables_name.Insert(0, Building_Name);
resultTest = resultTest.Where(x => x.Building.Trim() == id.Trim()).ToList();
Title = id + " Building";
}
//Get lables Line
if (type == 3)
{
var modelAuditOrg = await queryLables.Where(x => x.Line_ID == id).FirstOrDefaultAsync();
listLables = new List <string>()
{
"Overall",
"Dept " + modelAuditOrg.PDC_ID,
modelAuditOrg.Building,
modelAuditOrg.Line_ID
};
listLables_name = new List <string>()
{
"Overall",
"Dept " + modelAuditOrg.PDC_Name,
modelAuditOrg.Building_Name,
modelAuditOrg.Line_Name
};
resultTest = resultTest.Where(x => x.Line.Trim() == id.Trim()).ToList();
Title = modelAuditOrg.Line_Name;
}
//Chart
var chartModule1 = DataChart(dataChart.Where(x => x.AuditType2 == "Module1" && listLine.Contains(x.LineId)).
ToList(), listLables, type, id);
var chartModule3 = DataChart(dataChart.Where(x => x.AuditType2 == "Module3" && listLine.Contains(x.LineId)).
ToList(), listLables, type, id);
if (type == 0)
{
listLables_name = listLables_name.Select(x =>
{
x = x != "Overall" ? "Dept " + x : x;
return(x);
}).ToList();
Title = "Factory";
}
var month = DateTime.Now.Month - 1;
object result = new
{
chartModule1,
chartModule3,
listLables = listLables_name,
month,
resultTest,
Title
};
return(result);
}
public async Task <PagedList <SMEScoreRecordDto> > GetListSMEScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true, bool?check = false)
{
// SME là giá trị fix cứng
var paramAuditTypeIdBySME = await _repoTypeM.FindAll(x => x.Audit_Kind == "SME").Select(x => x.Audit_Type_ID).ToListAsync();
var queryAuditRateM = _repoRateM.FindAll().Where(x => paramAuditTypeIdBySME.Contains(x.Audit_Type_ID));
var queryAuditRateD = _repoRateD.FindAll();
var listAuditMes = _repoMesAuditOrg.FindAll(x => x.Status == 1);
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.AuditType1 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type1.Trim() == scoreRecordParam.AuditType1);
}
if (scoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == scoreRecordParam.AuditType2);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
try
{
}
catch (System.Exception)
{
throw;
}
//Get Module 1 & 3 =>SME Chart Static 9/8/2020
if (check == true)
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == "Module1" || x.Audit_Type2.Trim() == "Module3");
}
var data = queryAuditRateM.Join(listAuditMes, x => x.Line, t => t.Line_ID_2, (x, t) => new SMEScoreRecordDto
{
RecordId = x.Record_ID,
AuditDate = x.Record_Date,
AuditType = x.Audit_Type1,
AuditType2 = x.Audit_Type2,
LineId = x.Line,
Line_Name = t.Line_ID_2_Name,
UpdateBy = x.Updated_By,
UpdateTime = x.Updated_Time,
Rating0 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_0),
Rating1 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_1),
Rating2 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_2),
RatingNa = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA) == null ? 0 : queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA),
CheckAnswerAllYet = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID && y.Rate_NA == 0 && y.Rating_0 == 0 && y.Rating_1 == 0 && y.Rating_2 == 0).Count() > 0 ? false : true,
//Get data Static Chart
PDC = x.PDC,
Building = x.Building
}).OrderByDescending(x => x.UpdateTime);
return(await PagedList <SMEScoreRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
