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