LargeScreenBoardService.cs
8.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
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.LargetScreenBoardNotContainDevice);//月数据源查询条件
////周一、周末改为近七天，如果跨月不计算跨月的那几天
////计算前七天日期是否跨月，如果跨月，取当前月1号日期
//// 计算前七天的日期
//var sevenDaysAgo = month.AddDays(-6);
//var weekOne = sevenDaysAgo;
//// 判断是否跨月
//if (sevenDaysAgo.Month != month.Month)
//{
// // 如果跨月，取当前月1号日期
// DateTime firstDayOfCurrentMonth = new DateTime(month.Year, month.Month, 1);
// weekOne= firstDayOfCurrentMonth;
//}
//var weekend = month.ToString("yyyy-MM-dd 23:59:59");
//var sqlWhereWeek = string.Format(whereSql, weekOne, weekend, SystemVariable.LargetScreenBoardNotContainDevice);
var sql = GetSql(sqlWhereMonth);
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 = weekOne.ToString("MM-dd") + "~" + DateTime.Parse(weekend).ToString("MM-dd");
Tuple<DataSet, int, string> dss = new Tuple<DataSet, int, string>(ds, diffDay, monthStr);
////当天是否在本周内
//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],//每个项目月故障分
listMonthInfo = dss.Item1.Tables[1],//月故障数据明细
listMonthTopTen = dss.Item1.Tables[2],//前10
diffDay = dss.Item2,
monthStr = dss.Item3,//月日期范围
properlyTarget = dss.Item1.Tables[3],
factoryTarget = dss.Item1.Tables[4],
};
return response;
});
}
public string GetSql(string sqlWhereMonth)
{
var selectSql = "SELECT ProjectCode,projectName,sumTime=SUM(ErrorDuration) ";
var sqlMonth = selectSql + "FROM #listMonth t";
var groupBySql = " group by projectCode,projectName order by sumTime desc";
sqlMonth += 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)}')
)t;
----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
DELETE FROM #listMonth WHERE rownumber > 1 or ErrorDuration>60;
----------------故障時間本月开始listMonth0 表格1---------------------------
{sqlMonth}
----------------故障時間 月初、月末结束------------------------------------
SELECT * FROM #listmonth;
----------------故障持续时间最长的10条 表格2 Start--------------------------------
--按故障分，汇总持续分钟数
-- 基于 #listMonth 表进行查询和处理
WITH RankedAlarms AS (
SELECT
-- 使用 ROW_NUMBER() 函数生成行号，并转换为字母标识
LetterIdentifier = CHAR(64 + ROW_NUMBER() OVER (ORDER BY SUM(ErrorDuration) DESC)),
Alarm,
-- 计算每种 Alarm 类型的持续时间总和
sumTime = SUM(ErrorDuration),
-- 计算每种 Alarm 类型的出现次数
[count] = COUNT(1)
FROM
#listMonth
GROUP BY
Alarm
)
-- 从 RankedAlarms CTE 中选择数据并输出
SELECT
top 10
LetterIdentifier,
Alarm,
sumTime,
[count]
FROM
RankedAlarms
ORDER BY
-- 按照持续时间总和降序排序
sumTime DESC;
----------------故障持续时间最长的10条 表格2 End-----------------------------------
--妥善率目标
select dictValue FROM dbo.sys_dict_data WHERE dictLabel='properlyTarget'
--仓库可设定
select dictValue FROM dbo.sys_dict_data WHERE dictLabel='factoryTarget'";
return sql;
}
#endregion
}
}