LargeScreenBoardService.cs
10.4 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
using Hh.Mes.Common.DataTableTo;
using Hh.Mes.Common.log;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.ApiEntity;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.EnumEntitys;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.ViewModel.Equipment;
using Hh.Mes.POJO.WebEntity.configure;
using Hh.Mes.Service.Repository;
using Hh.Mes.Service.WebService.Equipment;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
namespace Hh.Mes.Service
{
public class LargeScreenBoardService : RepositorySqlSugar<sys_user>
{
#region 2号厂房看板IOT故障妥善率数据
public dynamic GetProjectAlarmAndProperRate(string yyyyMonth)
{
var response = new ResponseNew();
return ExceptionsHelp.Instance.ExecuteT(() =>
{
var month = Convert.ToDateTime(yyyyMonth);
var whereSql = @" where t1.CreateTime>='{0}' and t1.CreateTime<='{1}' and t1.EquipmentTypeCode not in ('{2}')";
//月初、月末
var monthStart = month.AddDays(1 - month.Day);
var monthEnd = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);
var sqlWhereMonth = string.Format(whereSql, monthStart.ToString("yyyy-MM-dd HH:mm:ss"), monthEnd.ToString("yyyy-MM-dd HH:mm:ss"), SystemVariable.IotNotContainDevice);//月数据源查询条件
//周一、周末
var weekOne = month.AddDays(0 - (Convert.ToInt16(month.DayOfWeek) == 0 ? 7 : Convert.ToInt16(month.DayOfWeek)) + 1).ToString("yyyy-MM-dd 00:00:00");
var weekend = month.AddDays(6 - (Convert.ToInt16(month.DayOfWeek) == 0 ? 7 : Convert.ToInt16(month.DayOfWeek)) + 1).ToString("yyyy-MM-dd 23:59:59");
var sqlWhereWeek = string.Format(whereSql, weekOne, weekend, SystemVariable.IotNotContainDevice);
var sql = GetSql(sqlWhereMonth, sqlWhereWeek);
var ds = Context.Ado.GetDataSetAll(sql);
var diffDay = DateTime.DaysInMonth(month.Year, month.Month);
var monthStr = monthStart.ToString("MM-dd") + "~" + monthEnd.ToString("MM-dd");
var weekStr = DateTime.Parse(weekOne).ToString("MM-dd") + "~" + DateTime.Parse(weekend).ToString("MM-dd");
Tuple<DataSet, int, string, string> dss = new Tuple<DataSet, int, string, string>(ds, diffDay, monthStr, weekStr);
//当天是否在本周内
var nowDay = DateTime.Now;
var selectDay = Convert.ToDateTime(yyyyMonth);
var startWeekDay = nowDay.AddDays(-(int)nowDay.DayOfWeek + 1).ToString("yyyy-MM-dd 00:00:00");//当前周的周一日期
var weekOnes = selectDay.AddDays(0 - (Convert.ToInt16(selectDay.DayOfWeek) == 0 ? 7 : Convert.ToInt16(selectDay.DayOfWeek)) + 1).ToString("yyyy-MM-dd 00:00:00");//当前所选周的周一日期
var isInterval = false;
if (startWeekDay == weekOnes) isInterval = true;
response.data = new
{
listMonth = dss.Item1.Tables[0],//月故障分
listWeek = dss.Item1.Tables[1],//周故障分
//listAlarmSum40 = dss.Item1.Tables[2],
diffDay = dss.Item2,
monthStr = dss.Item3,//月日期范围
weekStr = dss.Item4,//周日期范围
weekDefault = dss.Item1.Tables[3],
properlyTarget = dss.Item1.Tables[4],
factoryTarget = dss.Item1.Tables[5],
isInterval = isInterval,
projectAlarmSum = dss.Item1.Tables[6],
projectAlarmSumInfo = dss.Item1.Tables[7]
};
return response;
});
}
public string GetSql(string sqlWhereMonth, string sqlWhereWeek)
{
var selectSql = "SELECT ProjectCode,projectName,sumTime=SUM(ErrorDuration) ";
var sqlMonth = selectSql + "FROM #listMonth t";
var sqlWeek = selectSql + "FROM #listWeek t";
var sqlAlarm40 = "";
var groupBySql = " group by projectCode,projectName order by sumTime desc";
sqlMonth += groupBySql;
sqlWeek += groupBySql;
//只查询长沙、徐州、昆山三个项目的
//var projectKeys = base.GetProjectInfoKeys(base.sysUserApiAccount);
var projectKeys = new List<string>() { "16B7E641-A53E-404B-9237-33B8C19C7F55", "FA801C18-AB32-4ED9-B913-D5158883E800","A08FF98A-DFC5-4DFF-9ACF-74D7C2BDCF26"};
var sql = @$" -----------------本月公共数据源 ErrorDuration 已转化分）--------------------------------------------
IF OBJECT_ID('Tempdb..#listMonth') IS NOT NULL
DROP TABLE #listMonth;
SELECT * INTO #listMonth FROM (
SELECT rownumber=ROW_NUMBER() OVER (PARTITION BY EquipmentCode, CONVERT(VARCHAR(16), t1.CreateTime, 120), CONVERT(VARCHAR(16), t1.UpdateTime, 120)
ORDER BY EquipmentCode),
t1.CreateTime AS Created,
t1.UpdateTime AS Updated,
ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)) ,
t1.EquipmentCode,
t1.AlarmMessage + ';' + ISNULL(t1.Remark,'') AS Alarm,
t1.EquipmentName,
t1.EquipmentPropName,
t1.EquipmentTypeCode,
t1.ProjectCode,
p.projectName AS ProjectName
FROM dbo.daq_equipment_alarm_record t1 with(nolock)
LEFT JOIN dbo.base_project p ON p.projectCode = t1.ProjectCode
{sqlWhereMonth}
and p.keys in('{string.Join("','", projectKeys)}')
and t1.EquipmentTypeCode not in ('WeldRobotV2','WeldRobot')--排除机器人焊接设备的
)t;
----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
DELETE FROM #listMonth WHERE rownumber > 1;
-----------------本周公共数据源 #listWeek ErrorDuration 已转化分--------------------------------------------
IF OBJECT_ID('Tempdb..#listWeek') IS NOT NULL
DROP TABLE #listWeek;
SELECT *
INTO #listWeek
FROM
(
SELECT rownumber=ROW_NUMBER() OVER (PARTITION BY EquipmentCode, CONVERT(VARCHAR(16), t1.CreateTime, 120), CONVERT(VARCHAR(16), t1.UpdateTime, 120)
ORDER BY EquipmentCode),
t1.CreateTime AS created,
t1.UpdateTime AS updated,
ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)),
t1.EquipmentCode,
t1.EquipmentName,
t1.EquipmentPropName,
t1.Id AS EquipmentAlarmId,
t1.AlarmMessage+ ';' + ISNULL(t1.Remark,'') AS Alarm,
t1.ProjectCode,
t1.EquipmentTypeCode,
p.projectName AS ProjectName
FROM dbo.daq_equipment_alarm_record t1 with(nolock)
LEFT JOIN dbo.base_project p ON p.projectCode = t1.ProjectCode
{sqlWhereWeek}
and p.keys in('{string.Join("','", projectKeys)}')
and t1.EquipmentTypeCode not in ('WeldRobotV2','WeldRobot')
) t;
----------------listWeek 处理相同设备在同年月日时分数据>----------------
DELETE FROM #listWeek WHERE rownumber > 1;
----------------故障時間本月开始listMonth0 表格1-------------------------------
{sqlMonth}
----------------故障時間 月初、月末结束-------------------------------
----------------故障時間本周开始listWeek1 表格1-------------------------------
{sqlWeek}
----------------故障時間本周结束-------------------------------
--------------本周故障時間列表超過40分开始 ----------------
SELECT t.*,
eh.HandleInfo,
eh.HandleUser,
eh.Reason
FROM #listweek t
LEFT JOIN bus_equipment_alarm_handle eh ON eh.EquipmentAlarmRecordId = t.EquipmentAlarmId
WHERE t.ErrorDuration > 40 {sqlAlarm40}
ORDER BY t.ErrorDuration DESC;
-------------本周故障時間列表超過40分结束-------------------------------------
---weekDefault
SELECT dictValue FROM dbo.sys_dict_data WHERE dictLabel='weekDefault'
--妥善率目标
select dictValue FROM dbo.sys_dict_data WHERE dictLabel='properlyTarget'
--仓库可设定
select dictValue FROM dbo.sys_dict_data WHERE dictLabel='factoryTarget'
--项目设备报警统计
select t.ProjectCode, xAxis =convert(varchar(10),created,120),sumVal= SUM(t.ErrorDuration) from #listWeek t
group by t.ProjectCode,convert(varchar(10),created,120)
--项目设备报警统计明细信息
select xAxis =convert(varchar(10),created,120),timeVal=ErrorDuration,* from #listWeek ";
return sql;
}
#endregion
}
}