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)); }