BusEquipmentDataService.cs
9.3 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
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
//计算妥善率
//计算设备MTBF
//计算设备MTTR
/// <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;
});
}
/// <summary>
/// 根据时间段查询设备扩展指标（妥善率、OEE、MTTR、MTBF）
/// </summary>
/// <returns></returns>
public dynamic SelectProjectEquipmentsExtendIndex(string projectCode, string startDay, string endDay)
{
return ExceptionsHelp.Instance.ExecuteT(() =>
{
List<EquipmentStatusRecordEntity> statuRecordList = QueryEquipmentStatusRecord(projectCode, 2, startDay, endDay);
//项目下所有设备各数据
EquipmentIndexEntity entity = new EquipmentIndexEntity() { };
return "";
});
}
/// <summary>
/// 根据时间段查询单个设备的扩展指标
/// </summary>
/// <param name="equipmentCode"></param>
/// <param name="startDay"></param>
/// <param name="endDay"></param>
/// <returns></returns>
public List<EquipmentIndexEntity> SelectEquipmentExtendIndex(string equipmentCode,string startDay,string endDay)
{
return ExceptionsHelp.Instance.ExecuteT(() =>
{
List<EquipmentIndexEntity> indexList = new List<EquipmentIndexEntity>();//设备扩展指标集合
List<EquipmentStatusRecordEntity> record = QueryEquipmentStatusRecord(equipmentCode,1,startDay,endDay);
foreach(var i in record)
{
EquipmentIndexEntity equipment = new EquipmentIndexEntity();
equipment.ProjectCode = i.ProjectCode;
equipment.EquipmentCode = i.EquipmentCode;
equipment.EquipmentName = i.EquipmentName;
double allTime = i.RunningDuration + i.FreeDuration + i.ErrorDuration;
equipment.Oee = string.Format("{0:P2}", Math.Round(i.RunningDuration / allTime));
equipment.Proper = string.Format("{0:P2}",Math.Round(1 - (i.ErrorDuration / 24*60*60)));
equipment.Mtbf = string.Format("{0:P2}", Math.Round(1 - ((i.RunningDuration + i.FreeDuration) / i.ErrorCount))); ;
equipment.Mttr = string.Format("{0:P2}", Math.Round(1 - (i.ErrorDuration / i.ErrorCount))); ;
indexList.Add(equipment);
}
return indexList;
});
}
}
}