using Furion.DatabaseAccessor; using Furion.DatabaseAccessor.Extensions; using Furion.DependencyInjection; using Furion.DynamicApiController; using Furion.FriendlyException; using iWare.Wms.Core; using Mapster; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime; using System.Linq.Dynamic.Core; using iWare.Wms.Core.Enum; namespace iWare.Wms.Application { /// /// 产线产量统计服务 /// [ApiDescriptionSettings("数据查询服务", Name = "YieldAnalysis", Order = 100)] [Route("api/[Controller]")] [DisableOpLog] public class YieldAnalysisService : IYieldAnalysisService, IDynamicApiController, ITransient { private readonly IRepository _yieldAnalysisRep; private readonly IRepository _workPieceInfo; private readonly IRepository _EquipmentWorkingLog; private readonly IRepository _equipmentBaseInfoRep; private readonly IRepository _workPieceProcessRep; private readonly IRepository _shiftInfoRep; private readonly IRepository _knifeToolEquipmentInfoRep; private readonly IRepository _knifeToolEquipmentInfoLogRep; private readonly IRepository _sysDictTypeRep; private readonly IRepository _sysDictDataRep; //SCADAGatewayDbContext /// /// 产线产量统计构造 /// /// public YieldAnalysisService( IRepository yieldAnalysisRep, IRepository workPieceInfo, IRepository equipmentWorkingLog, IRepository equipmentBaseInfoRep, IRepository workPieceProcessRep, IRepository shiftInfoRep, IRepository knifeToolEquipmentInfoRep, IRepository knifeToolEquipmentInfoLogRep, IRepository sysDictTypeRep, IRepository sysDictDataRep) { _yieldAnalysisRep = yieldAnalysisRep; _workPieceInfo = workPieceInfo; _EquipmentWorkingLog = equipmentWorkingLog; _equipmentBaseInfoRep = equipmentBaseInfoRep; _workPieceProcessRep = workPieceProcessRep; _shiftInfoRep = shiftInfoRep; _knifeToolEquipmentInfoRep = knifeToolEquipmentInfoRep; _knifeToolEquipmentInfoLogRep = knifeToolEquipmentInfoLogRep; _sysDictTypeRep = sysDictTypeRep; _sysDictDataRep = sysDictDataRep; } /// /// 设备状态分析 /// /// /// [HttpGet("GetEquipmentStateAnalysisInfo")] public async Task> GetEquipmentStateAnalysisInfo([FromQuery] AnalysisCommInput input) { if(input.StartTime==null||input.EndTime==null|| (input.EndTime - input.StartTime).TotalDays>366) { throw Oops.Oh("传参异常,查询范围不能超过1年"); } DateTime currertTime = input.StartTime; List outPutLst = new List(); while (currertTime <= input.EndTime) { GetEquipmentStateAnalysisInfo infoOutput = new GetEquipmentStateAnalysisInfo(); infoOutput.Month = currertTime.ToString("yyyy-MM"); // 获取设备报警次数 List alterLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => !string.IsNullOrEmpty(x.FailureMsg)) .Where(x => x.FailureStartTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.FailureStartTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) .ToListAsync(); infoOutput.AlarmNum = alterLst.Count; // 获取设备报警持续时间 infoOutput.Duration = double.Parse(alterLst.Where(o => o.FailureStartTime != null && o.FailureEndTime != null) .Select(o => ((o.FailureEndTime.Value - o.FailureStartTime.Value).TotalHours)).Sum().ToString("f2")); // 获取设备状态信息 List stateLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => x.EquipmentStateStartTime.HasValue) .Where(x => x.EquipmentStateStartTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.EquipmentStateStartTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) .ProjectToType() .ToListAsync(); //EnumEquipmentCurrentState var statelist = from log in stateLst where log.EquipmentStateStartTime.HasValue //where log.EquipmentStateStartTime currertTime.AddDays(1 - currertTime.Day) //where log.EquipmentStateStartTime > currertTime.AddDays(1 - currertTime.Day).AddMonths(1).AddDays(-1) //where !string.IsNullOrEmpty(input.EquipmentID)?log.EquipmentID== input.EquipmentID:true group log by log.EquipmentState into loggroup select new EquipmentStateInfo { EquipmentStateName= EnumUtil.GetDescription((EnumEquipmentCurrentState)(Convert.ToInt32(loggroup.Key))), count=loggroup.Sum(o=>o.EquipmentStateTimeCount).ToString("f2"), }; var statusInfos = statelist.OrderByDescending(x => x.EquipmentStateName).ToList(); // 查询设备状态字典 补全缺少状态为0 var list = await _sysDictDataRep.DetachedEntities .Join(_sysDictTypeRep.DetachedEntities, d => d.TypeId, t => t.Id, (d, t) => new { d, t }) .Where(x => x.t.Name.Contains("设备当前状态")) .Where(x=>Convert.ToInt32(x.d.Code) > 0) .Select(u => new { u.d.Value }).ToListAsync(); var statusNames = list.Select(x => x.Value).ToList(); var statelistName = statusInfos.Select(x => x.EquipmentStateName).ToList(); var diffStatus = statusNames.Except(statelistName).ToList(); var newStatus = diffStatus.Select(x => new EquipmentStateInfo { EquipmentStateName = x, count = "0" }).ToList(); statusInfos.AddRange(newStatus); infoOutput.StatusRatiio = statusInfos.OrderByDescending(x => x.EquipmentStateName).ToList(); // 获取产能统计次数 List < WorkPieceInfo > pieceInfosLst = await _workPieceInfo.DetachedEntities.Where(x => x.WorkPieceState == 10) .Where(x => x.WorkPieceLastOfflineTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.WorkPieceLastOfflineTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .ToListAsync(); infoOutput.YieldCount = pieceInfosLst.Count; outPutLst.Add(infoOutput); // 月份加一 currertTime = currertTime.AddMonths(1); //outPutLst.Add(infoOutput); } return outPutLst; } /// /// 获取产能分析汇总接口 /// /// /// [HttpGet("GetYieldAnalysisInfo")] public async Task> GetYieldAnalysisInfo([FromQuery] AnalysisCommInput input) { if (input.StartTime == null || input.EndTime == null || (input.EndTime - input.StartTime).TotalDays > 366) { throw Oops.Oh("日期范围传参异常:必须传参且间隔不能超过1年"); } if(!input.Type.HasValue|| (input.Type!=1&& input.Type!=2)) { throw Oops.Oh("查询数据类型传参异常"); } List outPutLst = new List(); if (input.Type==1) { DateTime currertTime = input.StartTime; while (currertTime <= input.EndTime) { GetYieldAnalysisInfoOutput infoOutput = new GetYieldAnalysisInfoOutput(); infoOutput.CurrentYearMonth = currertTime.ToString("yyyy-MM"); // 获取设备报警次数 List alterLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => !string.IsNullOrEmpty(x.FailureMsg)) .Where(x => x.FailureStartTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.FailureStartTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) .ToListAsync(); infoOutput.AlertNumber = alterLst.Count; infoOutput.AlertDuration = alterLst.Where(o => o.FailureStartTime != null && o.FailureEndTime != null) .Select(o => (o.FailureEndTime.Value - o.FailureStartTime.Value).TotalHours).Sum().ToString("f2"); // 获取设备报警持续时间 // 获取产能统计次数 List pieceInfosLst = await _workPieceInfo.DetachedEntities.Where(x => x.WorkPieceState == (int)WorkPieceState.FinishedProducts) .Where(x => x.WorkPieceLastOfflineTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.WorkPieceLastOfflineTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .ToListAsync(); infoOutput.YieldCount = pieceInfosLst.Count; // 获取产能统计次数 List OKpieceInfosLst = await _workPieceInfo.DetachedEntities.Where(x => x.WorkPieceState == (int)WorkPieceState.FinishedProducts) .Where(x => x.WorkPieceLastOfflineTime >= currertTime.AddDays(1 - currertTime.Day)) .Where(x => x.WorkPieceLastOfflineTime < currertTime.AddDays(1 - currertTime.Day).AddMonths(1)) .Where(o => o.QualityState == (int)EnumQualityState.OK) .ToListAsync(); infoOutput.OKCount = OKpieceInfosLst.Count; outPutLst.Add(infoOutput); // 月份加一 currertTime = currertTime.AddMonths(1); //outPutLst.Add(infoOutput); } } else { DateTime currertTime = input.StartTime; while (currertTime <= input.EndTime) { GetYieldAnalysisInfoOutput infoOutput = new GetYieldAnalysisInfoOutput(); infoOutput.CurrentYearMonth = currertTime.ToString("yyyy-MM-dd"); // 获取设备报警次数 List alterLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => !string.IsNullOrEmpty(x.FailureMsg)) .Where(x => x.FailureStartTime >= currertTime.Date) .Where(x => x.FailureStartTime < currertTime.AddDays(1)) .Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) .ToListAsync(); infoOutput.AlertNumber = alterLst.Count; infoOutput.AlertDuration = alterLst.Where(o => o.FailureStartTime != null && o.FailureEndTime != null) .Select(o => (o.FailureEndTime.Value - o.FailureStartTime.Value).TotalHours).Sum().ToString("f2"); // 获取设备报警持续时间 // 获取产能统计次数 List pieceInfosLst = await _workPieceInfo.DetachedEntities.Where(x => x.WorkPieceState == (int)WorkPieceState.FinishedProducts) .Where(x => x.WorkPieceLastOfflineTime >= currertTime.Date) .Where(x => x.WorkPieceLastOfflineTime < currertTime.AddDays(1 )) .ToListAsync(); infoOutput.YieldCount = pieceInfosLst.Count; // 获取产能统计次数 List OKpieceInfosLst = await _workPieceInfo.DetachedEntities.Where(x => x.WorkPieceState == (int)WorkPieceState.FinishedProducts) .Where(x => x.WorkPieceLastOfflineTime >= currertTime.Date) .Where(x => x.WorkPieceLastOfflineTime < currertTime.AddDays(1)) .Where(o => o.QualityState == (int)EnumQualityState.OK) .ToListAsync(); infoOutput.OKCount = OKpieceInfosLst.Count; outPutLst.Add(infoOutput); // 天加一 currertTime = currertTime.AddDays(1); //outPutLst.Add(infoOutput); } } return outPutLst; } /// /// 获取MTBF(平均无故障工作时间) /// /// /// [HttpGet("GetMTBFInfo")] public async Task> GetMTBFInfo([FromQuery] AnalysisCommInput input) {//平均无故障时间MTBF = 总运行时间 / 发生故障次数 if (input.StartTime == null || input.EndTime == null|| input.StartTime >=input.EndTime) { throw Oops.Oh("传参异常"); } List mtbfList = new List(); // 获取设备状态信息(只查运行中的状态) List stateLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => x.EquipmentStateStartTime.HasValue) .Where(x => x.EquipmentStateStartTime >= input.StartTime) .Where(x => x.EquipmentStateStartTime < input.EndTime.AddDays(1)) .Where(o => o.EquipmentState == ((int)EnumEquipmentCurrentState.生产).ToString()) .ProjectToType() .ToListAsync(); // 获取设备报警次数 List alterLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => !string.IsNullOrEmpty(x.FailureMsg)) .Where(x => x.FailureStartTime >= input.StartTime) .Where(x => x.FailureStartTime <= input.EndTime.AddDays(1)) .ToListAsync(); GetMTBFOutput all = new GetMTBFOutput(); all.Code = "全产线"; all.Name = "全产线"; all.WorkTimeSum = double.Parse(stateLst.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); all.AlertCount = alterLst.Count; mtbfList.Add(all); var baseinfo = _equipmentBaseInfoRep.DetachedEntities.OrderBy(o=>o.EquipmentId).ToList(); foreach(var item in baseinfo) { GetMTBFOutput mtbf = new GetMTBFOutput(); mtbf.Code = item.EquipmentId; mtbf.Name = item.EquipmentName; var time= stateLst .Where(u => u.EquipmentID.Equals(item.EquipmentId)).ToList(); mtbf.WorkTimeSum = double.Parse(time.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); // 获取设备报警次数 var alter= alterLst.Where(o=>o.EquipmentID.Equals(item.EquipmentId)).ToList(); mtbf.AlertCount = alter.Count; mtbfList.Add(mtbf); } //EnumEquipmentCurrentState //var statelist = from log in stateLst // where log.EquipmentStateStartTime.HasValue // where log.EquipmentID.Equals(item.EquipmentId) // group log by new { log.EquipmentID, log.EquipmentName } into loggroup // select new GetMTBFOutput // { // Code = loggroup.Key.EquipmentID, // Name = loggroup.Key.EquipmentName, // WorkTimeSum = double.Parse(loggroup.Sum(o => o.EquipmentStateTimeCount).ToString("f2")), // }; return mtbfList; } /// /// 获取设备OEE ///OEE=可利用率* 性能率*质量率 ///可利用率 = 设备正常运行时间 / 总运行时间 ///性能率=设备实际产能/设备理论产能 ///质量率 = 合格产品数量 / 实际产出产品数量 /// /// /// [HttpGet("GetOEEInfo")] public async Task> GetOEEInfo([FromQuery] AnalysisCommInput input) { if (input.StartTime == null || input.EndTime == null || input.StartTime >= input.EndTime) { throw Oops.Oh("传参异常"); } List List = new List(); // 获取设备状态信息 List stateLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => x.EquipmentStateStartTime.HasValue) .Where(x => x.EquipmentStateStartTime >= input.StartTime) .Where(x => x.EquipmentStateStartTime < input.EndTime.AddDays(1)) //.Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) //.Where(o => o.EquipmentState != ((int)EnumEquipmentCurrentState.离线).ToString()) .ProjectToType() .ToListAsync(); // 获取产能统计次数 var previousSPC = await _workPieceProcessRep.DetachedEntities.Where(o => o.StartTime >= input.StartTime && o.StartTime < input.EndTime.AddDays(1) && o.OperationType != OperationType.SPC.ToString() ).ToListAsync(); //获取班次班组,用来获取计划工作时间 var shift = await _shiftInfoRep.DetachedEntities.ToListAsync(); double worktime = 0; foreach (var item in shift) { DateTime start = DateTime.Parse(DateTime.Now.Date.ToShortDateString() + " " + item.ShiftStartTime); DateTime end = DateTime.Parse(DateTime.Now.Date.ToShortDateString() + " " + item.ShiftEndTime); if (item.IsNextDay) { worktime += (end - start.Date).TotalHours; worktime += (end.Date.AddDays(1) - start).TotalHours; } else { worktime += (end - start).TotalHours; } } var baseinfo = _equipmentBaseInfoRep.DetachedEntities.OrderBy(o => o.EquipmentId).ToList(); foreach (EquipmentBaseInfo item in baseinfo) { GetOEEOutput mtbf = new GetOEEOutput(); mtbf.Code = item.EquipmentId; mtbf.Name = item.EquipmentName; //实际生产时间 var time = stateLst.Where(o => o.EquipmentState == ((int)EnumEquipmentCurrentState.生产).ToString()).ToList(); mtbf.WorkTimeSum = double.Parse(time.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); //计划生产时间 mtbf.PlanWorkTimeSum = worktime * (input.EndTime.AddDays(1) - input.StartTime).TotalDays; //正常运行时间(除故障,离线外所有开机时间) var NormalTime = stateLst.Where(o => o.EquipmentID.Equals(item.EquipmentId) &&o.EquipmentState != ((int)EnumEquipmentCurrentState.离线).ToString() && o.EquipmentState != ((int)EnumEquipmentCurrentState.故障).ToString()).ToList(); mtbf.NormalTimeSum = double.Parse(NormalTime.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); //总运行时间(除离线外其他所有时间) var alltime = stateLst.Where(o => o.EquipmentID.Equals(item.EquipmentId) && o.EquipmentState != ((int)EnumEquipmentCurrentState.离线).ToString()).ToList(); mtbf.AllTimeSum = double.Parse(alltime.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); //实际产量 var prieviou = previousSPC.Where(o => o.EquipmentID == item.EquipmentId).ToList(); mtbf.实际产量 = prieviou.Count(); //计划产量 if (item.TactTime == 0) { mtbf.理论产量 = 0; } else { mtbf.理论产量 = (int)(mtbf.PlanWorkTimeSum * 3600) / item.TactTime; } //合格产量 var hegecount = previousSPC.Where(o => o.EquipmentID == item.EquipmentId&&o.QualityState==(int)EnumQualityState.OK).ToList(); mtbf.合格产品数量= hegecount.Count(); List.Add(mtbf); } return List; } /// /// 设备性能率(另一种计算方式) /// /// /// < returns > [HttpGet("GetPerformanceInfo")] public async Task> GetPerformanceInfo([FromQuery] AnalysisCommInput input) {//设备性能率(%)=(实际产量 ÷ 理论产量)×(生产时间 ÷ 计划时间)×100% if (input.StartTime == null || input.EndTime == null || input.StartTime >= input.EndTime) { throw Oops.Oh("传参异常"); } List List = new List(); // 获取设备状态信息(只查运行中的状态) List stateLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => x.EquipmentStateStartTime.HasValue) .Where(x => x.EquipmentStateStartTime >= input.StartTime) .Where(x => x.EquipmentStateStartTime < input.EndTime.AddDays(1)) //.Where(!string.IsNullOrEmpty(input.EquipmentID), u => u.EquipmentID == input.EquipmentID) //.Where(o => o.EquipmentState == ((int)EnumEquipmentCurrentState.生产).ToString()) .ProjectToType() .ToListAsync(); //// 获取设备报警次数 //List alterLst = await _EquipmentWorkingLog.DetachedEntities.Where(x => !string.IsNullOrEmpty(x.FailureMsg)) // .Where(x => x.FailureStartTime >= input.StartTime) // .Where(x => x.FailureStartTime <= input.EndTime.AddDays(1)) // .ToListAsync(); // 获取产能统计次数 var previousSPC = await _workPieceProcessRep.DetachedEntities.Where(o => o.StartTime >= input.StartTime && o.StartTime < input.EndTime.AddDays(1) && o.OperationType != OperationType.SPC.ToString() ).ToListAsync(); //获取班次班组,用来获取计划工作时间 var shift = await _shiftInfoRep.DetachedEntities.ToListAsync(); double worktime = 0; foreach (var item in shift) { DateTime start = DateTime.Parse(DateTime.Now.Date.ToShortDateString() + " " + item.ShiftStartTime); DateTime end = DateTime.Parse(DateTime.Now.Date.ToShortDateString() + " " + item.ShiftEndTime); if (item.IsNextDay) { worktime += (end - start.Date).TotalHours; worktime += (end.Date.AddDays(1) - start).TotalHours; } else { worktime += (end - start).TotalHours; } } //GetPerformanceOutput all = new GetPerformanceOutput(); //all.Code = "全产线"; //all.Name = "全产线"; //all.WorkTimeSum = double.Parse(stateLst.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); //all.AlertCount = alterLst.Count; //mtbfList.Add(all); var baseinfo = _equipmentBaseInfoRep.DetachedEntities.OrderBy(o => o.EquipmentId).ToList(); foreach (EquipmentBaseInfo item in baseinfo) { GetPerformanceOutput mtbf = new GetPerformanceOutput(); mtbf.Code = item.EquipmentId; mtbf.Name = item.EquipmentName; //实际生产时间 var time = stateLst.Where(o => o.EquipmentState == ((int)EnumEquipmentCurrentState.生产).ToString()).ToList(); mtbf.WorkTimeSum = double.Parse(time.Sum(o => o.EquipmentStateTimeCount).ToString("f2")); //计划生产时间 mtbf.PlanWorkTimeSum = worktime * (input.EndTime.AddDays(1) - input.StartTime).TotalDays; //实际产量 var prieviou = previousSPC.Where(o => o.EquipmentID == item.EquipmentId).ToList(); mtbf.实际产量 = prieviou.Count(); //计划产量 if (item.TactTime == 0) { mtbf.理论产量 = 0; } else { mtbf.理论产量 = (int)(mtbf.PlanWorkTimeSum * 3600) / item.TactTime; } List.Add(mtbf); } return List; } /// /// 获取刀具分析 /// /// /// [HttpGet("GetKnifeToolAnalysisInfo")] public async Task GetKnifeToolAnalysisInfo([FromQuery] KnifeToolAnalysisInput input) { if (string.IsNullOrEmpty(input.WorkPieceID) || string.IsNullOrEmpty(input.EquipmentID)) { throw Oops.Oh("工件号和设备号必须得传参!"); } var wp =_workPieceProcessRep.Where(o => o.EquipmentID == input.EquipmentID&&o.WorkPieceID.Equals(input.WorkPieceID)).OrderByDescending(o=>o.StartTime).FirstOrDefault(); if(wp==null||wp.Id<=0) { throw Oops.Oh($"没有获取到工件【{input.WorkPieceID}】在设备【{input.EquipmentID}】上的生产信息!"); } GetKnifeToolAnalysisOutput analysis =new GetKnifeToolAnalysisOutput(); analysis.KnifeToolList = new List(); analysis.KnifeToolLoad = new List(); var infolist = _knifeToolEquipmentInfoRep.Where(o => o.ChangeStartTime < wp.StartTime && o.EquipmentID.Equals(input.EquipmentID)).ProjectToType().ToList(); var infoLoglist = _knifeToolEquipmentInfoLogRep.Where(o => o.ChangeStartTime < wp.StartTime && ((wp.EndTime>DateTime.Parse("1971/1/1")&& wp.EndTime().ToList(); analysis.KnifeToolList.AddRange(infolist); analysis.KnifeToolList.AddRange(infoLoglist); Random rd = new Random(); List list = new List(); for(int i=0;i< rd.Next(5,20); i++) { list.Add(rd.Next(9,18)); } analysis.KnifeToolLoad.Add(new KnifeToolLoad() { Name = "负载", Data = list.ToArray() }); for (int i = 0; i < rd.Next(5, 20); i++) { list.Add(rd.Next(9, 18)); } analysis.KnifeToolLoad.Add(new KnifeToolLoad() { Name = "负载1", Data = list.ToArray() }); for (int i = 0; i < rd.Next(5, 20); i++) { list.Add(rd.Next(9, 18)); } analysis.KnifeToolLoad.Add(new KnifeToolLoad() { Name = "负载2", Data = list.ToArray() }); return analysis; } } }