public void FillPPMByMachine(OeeRecord record, bool isByProduct) { using (var context = new SoheilEdmContext()) { var selectedMachineRepository = new Repository<SelectedMachine>(context); var processRepository = new Repository<Process>(context); var processReportRepository = new Repository<ProcessReport>(context); var causeRepository = new Repository<Cause>(context); var selectedMachineList = selectedMachineRepository.GetAll(); var processReportList = processReportRepository.GetAll(); var causeList = causeRepository.GetAll(); var prQuery = from selmachine in selectedMachineList.Where(x => x.StateStationActivityMachine != null && x.StateStationActivityMachine.Machine.Id == record.MachineId && x.Process != null && x.Process.StateStationActivity != null && x.Process.StartDateTime < record.End && x.Process.EndDateTime > record.Start) from processReport in processReportList.Where(pr => selmachine.Process != null && pr.Process != null && pr.Process.Id == selmachine.Process.Id && pr.StartDateTime < record.End && pr.EndDateTime > record.Start) from stpr in processReport.DefectionReports let start = (processReport.StartDateTime < record.Start) ? record.Start : processReport.StartDateTime let end = (processReport.EndDateTime > record.End) ? record.End : processReport.EndDateTime let hours = (end - start).TotalHours let ratio = hours / processReport.DurationSeconds let cycleTime = processReport.Process.StateStationActivity.CycleTime select new { productDefection = stpr.ProductDefection, //stoppage hours h = (stpr.LostTime + stpr.LostCount * cycleTime) * ratio, }; //ppm by product if (isByProduct) { var s1Query = from stoppageReport in prQuery group stoppageReport by stoppageReport.productDefection.Product into prodGroup let h = prodGroup.Sum(x => x.h) orderby h descending select new { h, txt = prodGroup.Key.Name, g = prodGroup, id = prodGroup.Key.Id }; double total1 = s1Query.Sum(x => x.h); foreach (var rowC1 in s1Query) { var detail1 = new OeeRecordDetail { ParentHours = total1, Hours = rowC1.h, Text = rowC1.txt, Id = rowC1.id }; record.DefectionDetails.Add(detail1); var s2Query = from stpr in prQuery where stpr.productDefection.Product == rowC1.g.Key group stpr by stpr.productDefection.Defection into defGroup let h = defGroup.Sum(x => x.h) orderby h descending select new { h, txt = defGroup.Key.Name, g = defGroup, id = defGroup.Key.Id }; double total2 = s2Query.Sum(x => x.h); foreach (var rowC2 in s2Query) { var detail2 = new OeeRecordDetail { ParentHours = total2, Hours = rowC2.h, Text = rowC2.txt, Id = rowC2.id, ParentId = rowC1.id }; detail1.SubRecords.Add(detail2); } } } //ppm by defection else { var s1Query = from stoppageReport in prQuery group stoppageReport by stoppageReport.productDefection.Defection into defGroup let h = defGroup.Sum(x => x.h) orderby h descending select new { h, txt = defGroup.Key.Name, g = defGroup, id = defGroup.Key.Id }; double total1 = s1Query.Sum(x => x.h); foreach (var rowC1 in s1Query) { var detail1 = new OeeRecordDetail { ParentHours = total1, Hours = rowC1.h, Text = rowC1.txt, Id = rowC1.id }; record.DefectionDetails.Add(detail1); var s2Query = from stpr in prQuery where stpr.productDefection.Defection == rowC1.g.Key group stpr by stpr.productDefection.Product into prodGroup let h = prodGroup.Sum(x => x.h) orderby h descending select new { h, txt = prodGroup.Key.Name, g = prodGroup, id = prodGroup.Key.Id }; double total2 = s2Query.Sum(x => x.h); foreach (var rowC2 in s2Query) { var detail2 = new OeeRecordDetail { ParentHours = total2, Hours = rowC2.h, Text = rowC2.txt, Id = rowC2.id, ParentId = rowC1.id }; detail1.SubRecords.Add(detail2); } } } } }
public void FillOEEStoppageByMachine(OeeRecord record) { using (var context = new SoheilEdmContext()) { var selectedMachineRepository = new Repository<SelectedMachine>(context); var processRepository = new Repository<Process>(context); var processReportRepository = new Repository<ProcessReport>(context); var causeRepository = new Repository<Cause>(context); var selectedMachineList = selectedMachineRepository.GetAll(); var processReportList = processReportRepository.GetAll(); var causeList = causeRepository.GetAll(); var prQuery = from selmachine in selectedMachineList.Where(x => x.StateStationActivityMachine != null && x.StateStationActivityMachine.Machine.Id == record.MachineId && x.Process != null && x.Process.StateStationActivity != null && x.Process.StartDateTime < record.End && x.Process.EndDateTime > record.Start) from processReport in processReportList.Where(pr => selmachine.Process != null && pr.Process != null && pr.Process.Id == selmachine.Process.Id && pr.StartDateTime < record.End && pr.EndDateTime > record.Start) from stpr in processReport.StoppageReports let start = (processReport.StartDateTime < record.Start) ? record.Start : processReport.StartDateTime let end = (processReport.EndDateTime > record.End) ? record.End : processReport.EndDateTime let hours = (end - start).TotalHours let ratio = hours / processReport.DurationSeconds let cycleTime = processReport.Process.StateStationActivity.CycleTime select new { PrId = processReport.Id, l3 = stpr.Cause, l2 = stpr.Cause.Parent, l1 = stpr.Cause.Parent.Parent, //stoppage hours h = (stpr.LostTime + stpr.LostCount * cycleTime) * ratio, }; var s1Query = from stpr in prQuery group stpr by stpr.l1 into g1 let h = g1.Sum(x => x.h) orderby h descending select new { h, txt = g1.Key.Name, g = g1, }; double total1 = s1Query.Sum(x => x.h); foreach (var rowC1 in s1Query) { var detail1 = new OeeRecordDetail { ParentHours = total1, Hours = rowC1.h, Text = rowC1.txt, Id = rowC1.g.Key.Id }; record.StoppageDetails.Add(detail1); var s2Query = from stpr in prQuery where stpr.l1 == rowC1.g.Key group stpr by stpr.l2 into g2 let h = g2.Sum(x => x.h) orderby h descending select new { h, txt = g2.Key.Name, g = g2 }; double total2 = s2Query.Sum(x => x.h); foreach (var rowC2 in s2Query) { var detail2 = new OeeRecordDetail { ParentHours = total2, Hours = rowC2.h, Text = rowC2.g.Key.Name, Id = rowC2.g.Key.Id, ParentId = rowC1.g.Key.Id }; detail1.SubRecords.Add(detail2); var s3Query = from stpr in prQuery where stpr.l2 == rowC2.g.Key group stpr by stpr.l3 into g3 let h = g3.Sum(x => x.h) orderby h descending select new { h, txt = g3.Key.Name, g = g3 }; double total3 = s3Query.Sum(x => x.h); foreach (var rowC3 in s3Query) { var detail3 = new OeeRecordDetail { ParentHours = total3, Hours = rowC3.h, Text = rowC3.g.Key.Name, Id = rowC3.g.Key.Id, ParentId = rowC2.g.Key.Id }; detail2.SubRecords.Add(detail3); } } } } }