public IQueryable <KepserverMachineDowntime> GetPlcDowntimeQueryable( DowntimeResourceParameter resourceParameter ) { if (resourceParameter == null) { throw new ArgumentNullException(nameof(resourceParameter)); } var qry = _context.KepserverMachineDowntimes .AsNoTracking() .Where( x => x.TimeStamp >= resourceParameter.StartDate && x.TimeStamp <= resourceParameter.EndDate ) .Where(x => x.DowntimeMinutes > 0) .AsQueryable(); if (!string.IsNullOrEmpty(resourceParameter.TagName)) { qry = qry.Where(x => x.TagName == resourceParameter.TagName); } if (!string.IsNullOrEmpty(resourceParameter.Line)) { qry = qry.Where(x => x.TagName.Contains(resourceParameter.Line.ToLower().Trim())); } return(qry); }
public async Task <IActionResult> GetDowntimeByOwner( [FromQuery] DowntimeResourceParameter resourceParameter ) { if (resourceParameter == null) { return(BadRequest()); } return(Ok(await _repo.GetDowntimeByOwner(resourceParameter))); }
public async Task <IActionResult> GetDowntimeIconics( [FromQuery] DowntimeResourceParameter @params ) { var initialDate = @params.Start.AddDays(-7); @params.End = @params.End.AddDays(1); @params.Dept = @params.Dept == "Plant" ? "" : @params.Dept; var data = await _repo .GetDowntimeIconics( initialDate, @params.End, @params.Dept, @params.MinDowntimeEvent, @params.MaxDowntimeEvent ) .ConfigureAwait(false); var spreadHours = _repo .SpreadHours(data.ToList()) .Where(x => x.StartStamp >= @params.Start && x.StartStamp <= @params.End); var result = spreadHours .GroupBy(x => new { x.Dept, x.Line }) .Select( x => new { Line = x.Key.Dept == "Machining" ? $"Line {x.Key.Line}" : $"{x.Key.Dept} {x.Key.Line}", TotalDowntime = Math.Round(x.Sum(t => t.Downtime), 0), MachineDetails = x.GroupBy(m => new { m.MachineName }) .Select( m => new { m.Key.MachineName, TotalDowntime = Math.Round(m.Sum(t => t.Downtime), 0), } ) .OrderByDescending(m => m.TotalDowntime) } ) .OrderByDescending(x => x.TotalDowntime) .ToList(); return(Ok(result)); }
public IQueryable <DowntimeEvent> GetDowntimeEvents( DowntimeResourceParameter resourceParameter ) { if (resourceParameter == null) { throw new ArgumentNullException(nameof(resourceParameter)); } var qry = _fmsbOeeContext.DowntimeEvents.AsNoTracking().AsQueryable(); if (resourceParameter.OeeId != Guid.Empty) { qry = qry.Where(x => x.OeeId == resourceParameter.OeeId); } if (resourceParameter.DowntimeEventType != DowntimeEventType.None) { qry = qry.Where(x => x.DowntimeEventType == resourceParameter.DowntimeEventType); } return(qry); }
public async Task <List <DowntimeDto> > GetDowntime(DowntimeResourceParameter parameter) { if (parameter == null) { throw new ArgumentNullException(nameof(parameter)); } var start = parameter.Start.AddMonths(-1); var end = parameter.End.AddDays(1).Date; //to get end of that day var machines = await _fmsbContext.MachineList .Where(x => x.MachineMapper != null) .ToListAsync() .ConfigureAwait(false); var owners = await GetDowntimeOwner().ConfigureAwait(false); // Get data in parallel var tasks = new List <Task>(); // Get call box downtime records var callBoxQry = _context.OverallCallbox.AsQueryable(); callBoxQry = callBoxQry.Where( x => x.RequestDateTime >= start && x.RequestDateTime <= end ); callBoxQry = parameter.Lines.Any() ? callBoxQry.Where(x => parameter.Lines.Contains(x.HxHLine)) : !string.IsNullOrEmpty(parameter.Line) ? callBoxQry.Where(x => x.HxHLine == parameter.Line) : callBoxQry; var callBoxTask = callBoxQry.ToListAsync(); // Get manual downtime records var manualDowntimeQry = _fmsbContext.DowntimeDataList2.AsQueryable(); manualDowntimeQry = manualDowntimeQry.Where( x => x.Shiftdate >= parameter.Start && x.Shiftdate <= parameter.End ); manualDowntimeQry = manualDowntimeQry.Where( x => x.DeptName.ToLower().Contains(parameter.Dept.Trim().ToLower()) ); manualDowntimeQry = manualDowntimeQry.Where( x => x.Shift.ToLower().Contains(parameter.Shift.Trim().ToLower()) ); manualDowntimeQry = parameter.Lines.Any() ? manualDowntimeQry.Where(x => parameter.Lines.Contains(x.MachineName)) : !string.IsNullOrEmpty(parameter.Line) ? manualDowntimeQry.Where( x => x.MachineName.ToLower() == parameter.Line.ToLower() ) : manualDowntimeQry; var manualDowntimeEntryTask = manualDowntimeQry.ToListAsync(); tasks.Add(callBoxTask); tasks.Add(manualDowntimeEntryTask); await Task.WhenAll(tasks).ConfigureAwait(false); var callBox = callBoxTask.Result; var manualDowntimeEntry = manualDowntimeEntryTask.Result; var spreadDowntimeData = SpreadHours(callBox); var spreadCallBoxData = spreadDowntimeData .Select( x => { var dept = machines.FirstOrDefault( m => string.Equals( m.MachineMapper, x.Line, StringComparison.CurrentCultureIgnoreCase ) )?.DeptName ?? x.Department; var line = machines.FirstOrDefault( m => string.Equals( m.MachineMapper, x.Line, StringComparison.CurrentCultureIgnoreCase ) )?.MachineName ?? x.Line; var dto = new DowntimeDto { Dept = dept, Line = line, Machine = x.Machine, Reason1 = x.PrimaryReason, Reason2 = x.SecondaryReason, Comments = $"<b>Operator Comment:</b> {x.OperatorComment} <br> <b>{x.Type} Comment:</b> {x.CompletionComment}", DowntimeLoss = (decimal)_dateShift.ElapsedTime( x.RequestDateTime, x.CompletedDateTime ), ShifDate = _dateShift.GetDateShift(x.RequestDateTime, dept).ShiftDate, Shift = _dateShift.GetDateShift(x.RequestDateTime, dept).Shift, ModifiedDate = x.RequestDateTime, Hour = _hour.GetHour(x.CompletedDateTime, dept), Type = x.Type, TypeColor = owners.FirstOrDefault(o => o.Owner == x.Type)?.Color ?? "" }; return(dto); } ) .Where(x => x.ShifDate >= parameter.Start && x.ShifDate <= parameter.End) .ToList(); var manualDowntime = manualDowntimeEntry .Select( x => new DowntimeDto { Dept = x.DeptName, Line = x.MachineName, Machine = x.MachineNumber, Reason1 = x.Reason1, Reason2 = x.Reason2, Comments = x.Comments, DowntimeLoss = x.Downtimeloss, ShifDate = x.Shiftdate, Shift = x.Shift, ModifiedDate = x.Modifieddate, Hour = x.Hour, Type = "Operator (Manual)", TypeColor = owners.FirstOrDefault(o => o.Owner == "Operator (Manual)")?.Color ?? "" } ) .ToList(); spreadCallBoxData.AddRange(manualDowntime); return(spreadCallBoxData .Where(x => x.Dept.ToLower().Contains(parameter.Dept.Trim().ToLower())) .OrderBy(x => x.ShifDate) .ThenBy(x => x.Shift) .ThenBy(x => x.Hour) .ToList()); }
public async Task <dynamic> GetDowntimeByOwner(DowntimeResourceParameter parameters) { var data = await GetDowntime(parameters).ConfigureAwait(false); var res = data.Where(x => x.DowntimeLoss > 0) .GroupBy(x => new { x.Type }) .Select( x => new { x.Key.Type, DowntimeLoss = x.Sum(t => t.DowntimeLoss), LineDetails = x.GroupBy(l => new { l.Line }) .Select( l => new { x.Key.Type, l.Key.Line, MachineLine = l.Key.Line, DowntimeLoss = l.Sum(t => t.DowntimeLoss), MahcineDetails = l.GroupBy(m => new { m.Machine }) .Select( m => new { x.Key.Type, l.Key.Line, m.Key.Machine, DowntimeLoss = m.Sum( t => t.DowntimeLoss ), ReasonDetails = m.GroupBy( r => new { r.Reason2 } ) .Select( r => new { x.Key.Type, l.Key.Line, m.Key.Machine, r.Key.Reason2, DowntimeLoss = r.Sum( t => t.DowntimeLoss ), } ) .Take(10) .OrderByDescending( r => r.DowntimeLoss ) } ) .OrderByDescending(m => m.DowntimeLoss) } ) .OrderByDescending(l => l.DowntimeLoss) } ) .OrderByDescending(x => x.DowntimeLoss) .ToList(); return(res); }
public async Task <IActionResult> GetDowntime( [FromQuery] DowntimeResourceParameter resourceParameter ) { if (resourceParameter == null) { return(BadRequest()); } try { var data = await _repo.GetDowntime(resourceParameter).ConfigureAwait(false); var categories = data.Select(x => new { Dept = x.Dept.ToUpper() }) .Distinct() .OrderBy(x => x.Dept.ToUpper()) .ToList(); var series = data.Select(x => new { x.Shift }) .Distinct() .OrderBy(x => x.Shift) .ToList(); var chartData = series.Select( s => new { seriesname = s.Shift, data = categories.Select( x => new { Dept = x.Dept.ToUpper(), TotalDowntime = data.Any( v => v.Dept.ToUpper() == x.Dept.ToUpper() && v.Shift == s.Shift ) ? data.Where( v => v.Dept.ToUpper() == x.Dept.ToUpper() && v.Shift == s.Shift ) .Sum(v => v.DowntimeLoss) : 0, ownerDetails = data.Where( o => o.Dept.ToUpper() == x.Dept.ToUpper() && o.Shift == s.Shift ) .GroupBy(o => new { o.Type, o.TypeColor }) .Select( o => new { o.Key.Type, o.Key.TypeColor, TotalDowntime = o.Sum(t => t.DowntimeLoss), LineDetails = o.GroupBy( l => new { l.Line, l.Machine } ) .Select( l => new { Line = l.Key.Machine.ToLower() != l.Key.Line.ToLower() ? $"{l.Key.Machine} ({l.Key.Line})" : l.Key.Machine, o.Key.TypeColor, TotalDowntime = l.Sum( t => t.DowntimeLoss ), Reason2Details = l.GroupBy( r => new { r.Reason2 } ) .Select( r => new { r.Key.Reason2, o.Key.TypeColor, TotalDowntime = r.Sum( t => t.DowntimeLoss ), DailyDetails = r.GroupBy( d => new { d.ShifDate } ) .Select( d => new { d.Key.ShifDate, o.Key.TypeColor, TotalDowntime = d.Sum( t => t.DowntimeLoss ) } ) .OrderBy( d => d.ShifDate ) } ) .OrderByDescending( r => r.TotalDowntime ) } ) .OrderByDescending(l => l.TotalDowntime) } ) .OrderByDescending(o => o.TotalDowntime) } ) } ); return(Ok(new { categories, chartData })); } catch (Exception e) { throw new Exception(e.Message); } }