BusEquipmentDataService.cs
12.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
using Hh.Mes.Common.Infrastructure;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Hh.Mes.Pojo.System;
using NPOI.POIFS.FileSystem;
using System.Data;
using Hh.Mes.POJO.ApiEntity;
namespace Hh.Mes.Service.Equipment
{
public class BusEquipmentDataService : RepositorySqlSugar<bus_equipment_alarm_handle>
{
//1、参数搜索支持单个设备和各个设备，汇总和每天数据
//2、运行时间、故障时间、空闲时间、故障次数、总时间
//3、过滤站台设备类型
//计算设备OEE：（运行时间/（运行时间+空闲时间+故障时间））*100%
//计算妥善率：（1-故障时间/总时间）*100%
//计算设备MTBF故障间隔时间： (运行hr+空闲时间hr)÷故障次数
//计算设备MTTR 故障维修时间：故障时间hr÷故障次数
/// <summary>
/// 根据时间段查询项目下所有设备扩展指标（妥善率、OEE、MTTR、MTBF）
/// </summary>
/// <returns></returns>
public dynamic SelectProjectEquipmentsExtendIndex(string projectName, string argument, int tag, string startDay, string endDay)
{
return ExceptionsHelp.Instance.ExecuteT(() =>
{
var response = new Response();
//计算天数
TimeSpan startDate = new TimeSpan(DateTime.Parse(startDay + " 00:00:01").Ticks);
TimeSpan endDate = new TimeSpan(DateTime.Parse(endDay + " 23:59:59").Ticks);
TimeSpan ts = endDate - startDate;
int days = ts.Days+1;//总天数
//如果没配置该项目的开机时间，则取默认值
projectName = Context.Queryable<SysDictData>().Any(x => x.DictType == "PowerOnTime" && x.DictLabel.Contains(projectName))?projectName:"default";
int openHour = Convert.ToInt32(Context.Queryable<SysDictData>().Where(x => x.DictType == "PowerOnTime" && x.DictLabel.Contains(projectName)).First().DictValue);//查询该项目配置的开机小时
//定义汇总指标参数
double sumRunningDuration = 0;
double sumFreeDuration = 0;
double sumErrorDuration = 0;
int sumErrorCount = 0;
//设备7天总时间
double sumProperTime = 0;
List<EquipmentIndexEntity> indexList = new List<EquipmentIndexEntity>();//设备扩展指标集合
List<EquipmentStatusRecordEntity> record = QueryEquipmentStatusRecord(argument, tag, startDay, endDay);
EquipmentIndexEntity entity = new EquipmentIndexEntity();//记录汇总指标数据
//判断是否查询出数据
if (record.Count > 0)
{
//1、查询设备每天的数据
foreach (var i in record)
{
EquipmentIndexEntity equipment = new EquipmentIndexEntity();
equipment.ProjectCode = i.ProjectCode;
equipment.EquipmentCode = i.EquipmentCode;
equipment.EquipmentName = i.EquipmentName;
equipment.Date = i.CreateTime;
equipment.RunningDuration = i.RunningDuration>0?Math.Round(i.RunningDuration,4):0;
equipment.FreeDuration = i.FreeDuration > 0 ? Math.Round(i.FreeDuration, 4) : 0;
equipment.ErrorDuration = i.ErrorDuration > 0 ? Math.Round(i.ErrorDuration, 4) : 0;
equipment.ErrorCount = i.ErrorCount;
equipment.SumDuration = openHour * 60;//开机总时间计算妥善率用，单位是分钟；
if (i.EquipmentCode.Contains("SRM")) equipment.SumDuration = 24 * 60 ;//堆垛机24小时不断电
sumProperTime += equipment.SumDuration;
double allTime = i.RunningDuration + i.FreeDuration + i.ErrorDuration;
if (allTime != 0)
{
equipment.Oee = string.Format("{0:P2}", Math.Round(i.RunningDuration / allTime, 4));
}
equipment.Proper = string.Format("{0:P2}", 1 - Math.Round(i.ErrorDuration / (equipment.SumDuration), 4));//当天妥善率
if (i.ErrorCount != 0)
{
equipment.Mtbf = Math.Round((i.RunningDuration / 60 + i.FreeDuration / 60) / i.ErrorCount, 4);
equipment.Mttr = Math.Round((i.ErrorDuration / 60) / i.ErrorCount, 4);
}
indexList.Add(equipment);
//为算汇总数据做准备，计算几天时间总和
sumRunningDuration += i.RunningDuration;
sumFreeDuration += i.FreeDuration;
sumErrorDuration += i.ErrorDuration;
sumErrorCount += i.ErrorCount;
}
//2、查询设备汇总指标
double allSumTime = sumRunningDuration + sumFreeDuration + sumErrorDuration;
entity.ProjectCode = record[0].ProjectCode;
entity.EquipmentCode = record[0].EquipmentCode;
entity.EquipmentName = record[0].EquipmentName;
entity.Date = record[0].CreateTime;
if (allSumTime!=0)
{
entity.Oee = string.Format("{0:P2}", Math.Round(sumRunningDuration / allSumTime, 4));
}
entity.Proper = string.Format("{0:P2}", 1 - Math.Round(sumErrorDuration / sumProperTime, 4));
if (sumErrorCount != 0)
{
entity.Mtbf = Math.Round((sumRunningDuration / 60 + sumFreeDuration / 60) / sumErrorCount, 4);
entity.Mttr = Math.Round((sumErrorDuration / 60) / sumErrorCount, 4);
}
}
//返回数据信息
response.Result = new
{
sumDt = entity,
detailDt = indexList
};
response.Count = indexList.Count;
return response;
});
}
/// <summary>
/// 查询设备时间段各状态持续时间【分钟数】 数据源方法
/// </summary>
/// <param name="argument">参数（设备编码或者项目编码）</param>
/// <param name="tag">标记 是传的设备号还是项目号（如果是项目号则查询项目下各个设备指标）</param>
/// <param name="startDay">开始时间（年月日)</param>
/// <param name="endDay">结束时间</param>
/// <returns></returns>
public List<EquipmentStatusRecordEntity> QueryEquipmentStatusRecord(string argument, int tag, string startDay, string endDay)
{
return ExceptionsHelp.Instance.ExecuteT(() =>
{
string whereArg = "";
string notInEquipmentType = SystemVariable.IotNotContainDevice;
string startTime = startDay + " 00:00:01";
string endTime = endDay + " 23:59:59";
if (tag == 1)
{
//查询单个设备数据
whereArg = $"EquipmentCode='{argument}'";
}
else if (tag == 2)
{
whereArg = $"ProjectCode='{argument}'";
}
string sql = @"SELECT
ProjectCode,
EquipmentCode,
EquipmentName,
ErrorCount = COUNT(*),
Status = 'Error',
FORMAT(CreateTime, 'yyyy-MM-dd') AS CreateTime,
Duration = SUM(ErrorDuration)
FROM dbo.daq_equipment_alarm_record
WHERE
{3}
AND EquipmentTypeCode NOT IN ('{0}')
AND CreateTime>='{1}' AND CreateTime<='{2}'
--AND IsEnd=1
GROUP BY EquipmentCode,EquipmentName,FORMAT(CreateTime,'yyyy-MM-dd'),ProjectCode
UNION ALL
SELECT
ProjectCode,
EquipmentCode,
EquipmentName,
ErrorCount=0,
Status,
FORMAT(CreateTime,'yyyy-MM-dd') AS CreateTime,
SUM(StatusDuration) AS Duration
FROM dbo.daq_equipment_status_record_history
WHERE
{3}
AND EquipmentTypeCode NOT IN ('{0}')
AND CreateTime>='{1}'
AND CreateTime<='{2}'
--AND IsEnd=1
AND Status<>'Failure'
GROUP BY EquipmentCode,EquipmentName,FORMAT(CreateTime,'yyyy-MM-dd'),Status,ProjectCode";
sql = string.Format(sql, notInEquipmentType, startTime, endTime, whereArg);
DataTable dt = Context.Ado.GetDataTable(sql);
//处理数据统一 一行 方便计算
var dt_new = dt.AsEnumerable()
.GroupBy(row => new
{
ProjectCode = row["ProjectCode"].ToString(),
EquipmentCode = row["EquipmentCode"].ToString(),
EquipmentName = row["EquipmentName"].ToString(),
CreateTime = row["CreateTime"].ToString()
})
.OrderBy(row => row.Key.EquipmentCode)
.OrderBy(row => row.Key.CreateTime)
.ToList();
//新的集合保存统一的数据
List<EquipmentStatusRecordEntity> statusList = new List<EquipmentStatusRecordEntity>();
foreach (var i in dt_new)
{
EquipmentStatusRecordEntity status = new EquipmentStatusRecordEntity();
status.ProjectCode = i.Key.ProjectCode;
status.EquipmentCode = i.Key.EquipmentCode;
status.EquipmentName = i.Key.EquipmentName;
status.CreateTime = i.Key.CreateTime;
statusList.Add(status);
}
//遍历新的集合加上各状态持续时间
foreach (var i in statusList)
{
var matchDt = dt.AsEnumerable()
.Where(x => x.Field<string>("ProjectCode") == i.ProjectCode && x.Field<string>("EquipmentCode") == i.EquipmentCode && x.Field<string>("CreateTime") == i.CreateTime)
.Select(x => new
{
ProjectCode = x.Field<string>("ProjectCode"),
EquipmentCode = x.Field<string>("EquipmentCode"),
EquipmentName = x.Field<string>("EquipmentName"),
ErrorCount = x.Field<int>("ErrorCount"),
Status = x.Field<string>("Status"),
CreateTime = x.Field<string>("CreateTime"),
Duration = x.Field<double>("Duration") / 60//处理时间为分
})
.ToList();
i.RunningDuration = matchDt.Where(x => x.Status == "Running").FirstOrDefault()?.Duration ?? 0;
i.FreeDuration = matchDt.Where(x => x.Status == "Free").FirstOrDefault()?.Duration ?? 0;
i.ErrorDuration = matchDt.Where(x => x.Status == "Error").FirstOrDefault()?.Duration ?? 0;
i.ErrorCount = matchDt.Where(x => x.Status == "Error").FirstOrDefault()?.ErrorCount ?? 0;
}
return statusList;
});
}
}
}