赖素文
authored
11 months ago
1
2
using Hh.Mes.Common.DataTableTo ;
using Hh.Mes.Common.log ;
3
using Hh.Mes.Pojo.System ;
4
using Hh.Mes.POJO.ApiEntity ;
5
6
7
using Hh.Mes.POJO.Entity ;
using Hh.Mes.POJO.Response ;
using Hh.Mes.Service.Repository ;
8
using Newtonsoft.Json ;
赖素文
authored
9 months ago
9
using NPOI.POIFS.FileSystem ;
10
using NPOI.SS.Formula.Functions ;
11
using SqlSugar ;
12
using SqlSugar.Extensions ;
13
14
using System ;
using System.Collections.Generic ;
赖素文
authored
11 months ago
15
16
using System.Data ;
using System.Linq ;
赖素文
authored
9 months ago
17
using System.Linq.Expressions ;
18
using System.Security.Claims ;
19
using System.Text ;
20
using static Microsoft . AspNetCore . Hosting . Internal . HostingApplication ;
21
22
23
24
25
26
namespace Hh.Mes.Service
{
public class BulletinBoardService : RepositorySqlSugar < sys_user >
{
/// < summary >
赖素文
authored
11 months ago
27
/// 故障报告
28
/// </ summary >
赖素文
authored
11 months ago
29
30
31
32
33
/// < param name = "yyyyMonth" > 日期 </ param >
/// < param name = "project" > 项目 </ param >
/// < param name = "equipmentType" > 设备类型 </ param >
/// < returns ></ returns >
public dynamic GetFaultReport ( string yyyyMonth , string projectCode , string equipmentTypeCode )
34
{
赖素文
authored
11 months ago
35
var response = new ResponseNew ();
36
37
return ExceptionsHelp . Instance . ExecuteT (() =>
{
赖素文
authored
11 months ago
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
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 , monthEnd , 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 , projectCode , equipmentTypeCode );
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
67
{
赖素文
authored
11 months ago
68
69
70
71
72
73
74
75
76
77
78
79
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 ],
赖素文
authored
11 months ago
80
81
isInterval = isInterval ,
82
projectAlarmSum = dss . Item1 . Tables [ 6 ],
赖素文
authored
11 months ago
83
projectAlarmSumInfo = dss . Item1 . Tables [ 7 ]
84
85
86
};
return response ;
});
赖素文
authored
11 months ago
87
88
89
90
91
92
93
}
public string GetSql ( string sqlWhereMonth , string sqlWhereWeek , string projectCode , string equipmentTypeCode )
{
var selectSql = "SELECT ProjectCode,projectName,sumTime=SUM(ErrorDuration) " ;
var sqlMonth = selectSql + "FROM #listMonth t" ;
var sqlWeek = selectSql + "FROM #listWeek t" ;
赖素文
authored
11 months ago
94
var sqlAlarm40 = "" ;
95
if (! string . IsNullOrEmpty ( projectCode ))
赖素文
authored
11 months ago
96
97
98
99
{
var whereSql = $ " WHERE t.projectCode='{projectCode}'" ;
sqlMonth += whereSql ;
sqlWeek += whereSql ;
赖素文
authored
11 months ago
100
sqlAlarm40 = $ " and t.projectCode='{projectCode}'" ;
赖素文
authored
11 months ago
101
102
103
104
105
}
var groupBySql = " group by projectCode,projectName order by sumTime desc" ;
sqlMonth += groupBySql ;
sqlWeek += groupBySql ;
赖素文
authored
9 months ago
106
var projectKeys = base . GetProjectInfoKeys ( base . sysUserApiAccount );
赖素文
authored
11 months ago
107
var sql = @$" ----------------- 本月公共数据源 ErrorDuration 已转化分) --------------------------------------------
赖素文
authored
11 months ago
108
109
110
IF OBJECT_ID ( ' Tempdb .. # listMonth ' ) IS NOT NULL
DROP TABLE # listMonth ;
SELECT * INTO # listMonth FROM (
赖素文
authored
11 months ago
111
112
113
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 ,
赖素文
authored
11 months ago
114
t1 . UpdateTime AS Updated ,
115
ErrorDuration = CAST ( t1 . ErrorDuration / 60.0 as DECIMAL ( 10 , 2 )) ,
赖素文
authored
11 months ago
116
t1 . EquipmentCode ,
117
t1 . AlarmMessage + ';' + ISNULL ( t1 . Remark , '' ) AS Alarm ,
赖素文
authored
11 months ago
118
119
120
121
t1 . EquipmentName ,
t1 . EquipmentPropName ,
t1 . EquipmentTypeCode ,
t1 . ProjectCode ,
赖素文
authored
11 months ago
122
123
124
125
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 }
赖素文
authored
9 months ago
126
and p . keys in ( ' { string . Join ( "','" , projectKeys )} ' )
赖素文
authored
11 months ago
127
128
) t ;
----------------< # listMonth 处理相同设备在同年月日时分数据 >----------------
赖素文
authored
11 months ago
129
DELETE FROM # listMonth WHERE rownumber > 1 ;
赖素文
authored
11 months ago
130
赖素文
authored
11 months ago
131
----------------- 本周公共数据源 # listWeek ErrorDuration 已转化分 --------------------------------------------
赖素文
authored
11 months ago
132
133
134
135
136
137
IF OBJECT_ID ( ' Tempdb .. # listWeek ' ) IS NOT NULL
DROP TABLE # listWeek ;
SELECT *
INTO # listWeek
FROM
(
赖素文
authored
11 months ago
138
139
140
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 ,
赖素文
authored
11 months ago
141
t1 . UpdateTime AS updated ,
142
ErrorDuration = CAST ( t1 . ErrorDuration / 60.0 as DECIMAL ( 10 , 2 )),
赖素文
authored
11 months ago
143
144
145
146
t1 . EquipmentCode ,
t1 . EquipmentName ,
t1 . EquipmentPropName ,
t1 . Id AS EquipmentAlarmId ,
147
t1 . AlarmMessage + ';' + ISNULL ( t1 . Remark , '' ) AS Alarm ,
赖素文
authored
11 months ago
148
149
150
t1 . ProjectCode ,
t1 . EquipmentTypeCode ,
p . projectName AS ProjectName
151
FROM dbo . daq_equipment_alarm_record t1 with ( nolock )
赖素文
authored
11 months ago
152
153
LEFT JOIN dbo . base_project p ON p . projectCode = t1 . ProjectCode
{ sqlWhereWeek }
赖素文
authored
9 months ago
154
and p . keys in ( ' { string . Join ( "','" , projectKeys )} ' )
赖素文
authored
11 months ago
155
156
157
) t ;
---------------- listWeek 处理相同设备在同年月日时分数据 >----------------
赖素文
authored
11 months ago
158
DELETE FROM # listWeek WHERE rownumber > 1 ;
赖素文
authored
11 months ago
159
160
161
162
163
164
165
166
167
---------------- 故障時間本月开始 listMonth0 表格 1 -------------------------------
{ sqlMonth }
---------------- 故障時間 月初、月末结束 -------------------------------
---------------- 故障時間本周开始 listWeek1 表格 1 -------------------------------
{ sqlWeek }
---------------- 故障時間本周结束 -------------------------------
赖素文
authored
11 months ago
168
-------------- 本周故障時間列表超過 40 分开始 ----------------
赖素文
authored
11 months ago
169
170
171
172
173
SELECT t .*,
eh . HandleInfo ,
eh . HandleUser ,
eh . Reason
FROM # listweek t
赖素文
authored
11 months ago
174
LEFT JOIN bus_equipment_alarm_handle eh ON eh . EquipmentAlarmRecordId = t . EquipmentAlarmId
175
WHERE t . ErrorDuration > 40 { sqlAlarm40 }
赖素文
authored
11 months ago
176
177
178
179
180
181
182
183
184
185
186
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 '
赖素文
authored
11 months ago
187
188
-- 项目设备报警统计
189
select t . ProjectCode , xAxis = convert ( varchar ( 10 ), created , 120 ), sumVal = SUM ( t . ErrorDuration ) from # listWeek t
赖素文
authored
11 months ago
190
group by t . ProjectCode , convert ( varchar ( 10 ), created , 120 )
赖素文
authored
11 months ago
191
赖素文
authored
11 months ago
192
-- 项目设备报警统计明细信息
赖素文
authored
11 months ago
193
select xAxis = convert ( varchar ( 10 ), created , 120 ), timeVal = ErrorDuration ,* from # listWeek " ;
赖素文
authored
11 months ago
194
195
196
197
198
199
200
return sql ;
}
/// < summary >
/// 故障統計分析
/// </ summary >
/// < returns ></ returns >
201
public dynamic FaultStatistics ( string begin , string end , string projectCode , string equipmentCode )
赖素文
authored
11 months ago
202
203
204
205
206
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
var whereSql = "" ;
赖素文
authored
11 months ago
207
if (! string . IsNullOrEmpty ( projectCode )) whereSql = $ "and t1.projectCode='{projectCode}'" ;
208
if (! string . IsNullOrEmpty ( equipmentCode )) whereSql = $ " and t1.equipmentCode='{equipmentCode}'" ;
赖素文
authored
9 months ago
209
210
211
var projectKeys = base . GetProjectInfoKeys ( base . sysUserApiAccount );
var sql = GetAlarmSql ( whereSql , begin , end , projectKeys );
赖素文
authored
11 months ago
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
var dt = Context . Ado . GetDataTable ( sql );
response . data = dt ;
response . count = dt . Rows . Count ;
response . code = 200 ;
return response ;
}, catchRetrunValue : "list" );
}
/// < summary >
/// 妥善率报表查询
/// </ summary >
/// < param name = "startTime" ></ param >
/// < param name = "endTime" ></ param >
/// < param name = "eqCode" ></ param >
/// < param name = "yearOrdaySelectVal" > 传年或者日 </ param >
/// < param name = "timeFlag" > 值有 year 、 month 、 day </ param >
/// < returns ></ returns >
230
public dynamic GetProperRate ( string startTime , string endTime , string yearOrdaySelectVal , string timeFlag , string projectCode , string equipmentCode )
赖素文
authored
11 months ago
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
if ( timeFlag == "year" )
{
startTime = yearOrdaySelectVal + "-01-01" ;
endTime = yearOrdaySelectVal + "-12-31" ;
}
else if ( timeFlag == "day" )
{
startTime = yearOrdaySelectVal ;
endTime = yearOrdaySelectVal ;
}
var whereSql = "" ;
247
if (! string . IsNullOrEmpty ( projectCode ) && ! string . IsNullOrEmpty ( equipmentCode ))
赖素文
authored
11 months ago
248
{
249
whereSql = $ "and t.projectCode='{projectCode}' and t.EquipmentCode='{equipmentCode}'" ;
赖素文
authored
11 months ago
250
}
251
if (! string . IsNullOrEmpty ( projectCode ) && string . IsNullOrEmpty ( equipmentCode ))
赖素文
authored
11 months ago
252
{
253
whereSql = $ "and t.projectCode='{projectCode}'" ;
赖素文
authored
11 months ago
254
}
255
if ( string . IsNullOrEmpty ( projectCode ) && ! string . IsNullOrEmpty ( equipmentCode ))
赖素文
authored
11 months ago
256
{
257
whereSql = $ "and t.EquipmentCode='{equipmentCode}'" ;
赖素文
authored
11 months ago
258
259
260
261
262
}
var startDate = startTime + " 00:00:00" ;
var endDate = endTime + " 23:59:59" ;
263
264
var sql = GetAlarmSql ( "" , startDate , endDate ) + GetRateSql ( timeFlag , whereSql );
var ds = Context . Ado . GetDataSetAll ( sql );
赖素文
authored
11 months ago
265
266
267
268
var resDt = ds . Tables [ 1 ];
// 查询项目所有设备
269
if ( string . IsNullOrEmpty ( equipmentCode ))
270
{
271
resDt = HandleDt ( resDt , projectCode , startTime );
272
273
274
}
response . data = resDt ;
275
response . count = ds . Tables [ 1 ]. Rows . Count ;
赖素文
authored
11 months ago
276
277
278
279
return response ;
}, catchRetrunValue : "list" );
}
280
public string GetRateSql ( string timeFlag , string whereSql )
赖素文
authored
11 months ago
281
{
282
var sql = @" select { 0 } as date ,
赖素文
authored
11 months ago
283
sumTime = sum ( ErrorDuration ),
284
ProjectName , EquipmentCode , EquipmentName , Sequence
赖素文
authored
11 months ago
285
from # tempdbRate t
286
287
where 1 = 1 { 1 }
group by { 0 },
288
289
ProjectName , EquipmentCode , EquipmentName , Sequence
order by Sequence " ;
290
291
if ( timeFlag == "year" )
{
292
sql = string . Format ( sql , "CONVERT(varchar(7),created,120)" , whereSql );// 年月分组
赖素文
authored
11 months ago
293
294
295
}
else
{
296
sql = string . Format ( sql , "CONVERT(varchar,created,23)" , whereSql );// 年月日分组
赖素文
authored
11 months ago
297
}
298
return sql ;
赖素文
authored
11 months ago
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
}
/// < summary >
/// 查询故障月份对比数据(只查三个月)
/// </ summary >
/// < param name = "year" ></ param >
/// < param name = "month" ></ param >
/// < param name = "projectCode" > 项目 </ param >
/// < param name = "equipmentTypeCode" > 设备类型 </ param >
/// < returns ></ returns >
public dynamic GetFailureMonthCompare ( int year , int month , string projectCode , string equipmentTypeCode )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
if ( string . IsNullOrEmpty ( projectCode ))
{
response . code = 500 ;
response . status = false ;
response . message = "请选择一个项目" ;
return response ;
}
DateTime startTime = new DateTime ( year , month , 1 ). AddMonths (- 2 );// 所选日期近三月的第一天
DateTime endTime = new DateTime ( year , month , 1 , 23 , 59 , 59 ). AddMonths ( 1 ). AddDays (- 1 );// 得出最后一天
324
325
326
327
328
329
330
331
332
333
var whereSql = "" ;
if (! string . IsNullOrEmpty ( projectCode ) && ! string . IsNullOrEmpty ( equipmentTypeCode ))
{
whereSql = $ "and t1.projectCode='{projectCode}' and t1.equipmentTypeCode='{equipmentTypeCode}'" ;
}
if (! string . IsNullOrEmpty ( projectCode ) && string . IsNullOrEmpty ( equipmentTypeCode ))
{
whereSql = $ "and t1.projectCode='{projectCode}'" ;
}
var sql = GetAlarmSql ( whereSql , startTime . ToString (), endTime . ToString ());
334
sql = sql + @"
335
336
337
338
339
340
SELECT Alarm = t1 . AlarmMessage ,
SumTime = SUM ( t1 . ErrorDuration ) ,
YearMonth = CONVERT ( CHAR ( 7 ), t1 . CreateTime , 120 )
FROM # tempdbRate t1
GROUP BY CONVERT ( CHAR ( 7 ), t1 . CreateTime , 120 ), t1 . AlarmMessage
ORDER BY CONVERT ( CHAR ( 7 ), t1 . CreateTime , 120 ); " ;
341
DataSet ds = Context . Ado . GetDataSetAll ( sql );
赖素文
authored
11 months ago
342
// 处理没有数据的赋值 0
343
var dt = ds . Tables [ 1 ];
赖素文
authored
11 months ago
344
var alarmDt = dt . AsEnumerable (). GroupBy ( x => x [ "Alarm" ]). Distinct (). Select ( x => x . Key ). ToList ();
赖素文
authored
11 months ago
345
346
var sumTime = dt . GetColumnValues < decimal >( "SumTime" ). Sum ();
var sumMsgInfo = "故障月份对比 " ;
赖素文
authored
11 months ago
347
348
349
350
for ( int i = 0 ; i < 3 ; i ++)
{
DateTime t = new DateTime ( year , month , 1 ). AddMonths (- i );
string currentTime = t . ToString ( "yyyy-MM" );
赖素文
authored
11 months ago
351
352
var totalSumTime = dt . CalculateSumTime ( "YearMonth" , "SumTime" , currentTime );
sumMsgInfo += currentTime + "故障分钟:" + totalSumTime + "," ;
赖素文
authored
11 months ago
353
354
355
356
357
358
359
360
361
362
363
foreach ( string alarm in alarmDt )
{
var isHave = dt . AsEnumerable (). Any ( x => x [ "YearMonth" ]. ToString () == currentTime && x [ "Alarm" ]. ToString (). TrimEnd () == alarm );
if (! isHave )
{
dt . Rows . Add ( alarm , 0 , currentTime );
}
}
}
response . code = 200 ;
364
365
366
response . data = new
{
data = dt ,
赖素文
authored
11 months ago
367
allAlarm = ds . Tables [ 0 ],
368
sumMsgInfo = sumMsgInfo + " 总故障时间:" + sumTime + "(单位分钟)"
369
};
赖素文
authored
11 months ago
370
371
372
response . count = dt . Rows . Count ;
return response ;
}, catchRetrunValue : "list" );
373
}
374
375
376
377
378
379
380
381
382
383
384
385
386
387
/// < summary >
/// 项目月 四周 故障次数 & 时间
/// </ summary >
/// < param name = "projectCode" > 项目号 </ param >
/// < param name = "currentDate" > 时间日期 </ param >
/// < returns ></ returns >
public dynamic GetFailureMonthWeekCountAndTime ( string projectCode , DateTime currentDate )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
List < EquipmentAlarmCountTimeEntity > list = new List < EquipmentAlarmCountTimeEntity >();
// 数据源查出来
388
389
390
391
392
393
394
395
396
var whereSql = "" ;
if (! string . IsNullOrEmpty ( projectCode ))
{
whereSql = $ "and t1.projectCode='{projectCode}'" ;
}
var start = currentDate . AddDays (- 28 ). ToString ();
var end = currentDate . ToString ();
var sql = GetAlarmSql ( whereSql , start , end );
DataTable allAlarmDt = Context . Ado . GetDataTable ( sql );
397
398
399
400
401
402
// 近四周
for ( int i = 0 ; i < 4 ; i ++)
{
EquipmentAlarmCountTimeEntity entity = new EquipmentAlarmCountTimeEntity ();
DateTime startTime = currentDate . AddDays (-( 7 * ( i + 1 )));
DateTime endTime = currentDate . AddDays (-( 7 * i ));
403
404
405
var dt = allAlarmDt . AsEnumerable ()
. Where ( i => i . Field < DateTime >( "CreateTime" ) >= startTime && i . Field < DateTime >( "CreateTime" ) < endTime )
. ToList ();
406
407
entity . WeekNo = $ "第{4 - i}周" ;
408
409
entity . TimePeriod = $ "{startTime.ToString().Replace(" 0 : 00 : 00 ", "")}~{endTime.ToString().Replace(" 0 : 00 : 00 ", "")}" ;
entity . AlarmSumTime = ( decimal ) dt . Sum ( i => i . Field < decimal >( "ErrorDuration" ));
410
entity . AlarmCount = dt . Count ;
411
entity . AlarmListData = dt . Count > 0 ? dt . CopyToDataTable () : null ;
412
413
list . Add ( entity );
}
414
response . count = allAlarmDt . Rows . Count ;
415
response . data = list ;
416
417
418
419
return response ;
});
}
420
421
422
423
424
425
426
/// < summary >
/// 项目设备故障一天每小时故障次数
/// </ summary >
/// < param name = "projectCode" ></ param >
/// < param name = "equipmentCode" ></ param >
/// < param name = "currentDate" ></ param >
/// < returns ></ returns >
427
428
429
430
431
public dynamic GetFailureEveryHourCount ( string projectCode , string equipmentCode , DateTime currentDate )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
432
433
434
435
436
string startTime = currentDate . ToString ( "yyyy-MM-dd" ) + " 00:00:00" ;
string endTime = currentDate . ToString ( "yyyy-MM-dd" ) + " 23:59:59" ;
var sqlWhere = "" ;
if (! string . IsNullOrEmpty ( projectCode )) sqlWhere = $ "and t1.projectCode='{projectCode}'" ;
var sql = GetAlarmSql ( sqlWhere , startTime , endTime );
437
438
439
440
441
var whereSql = "" ;
if (! string . IsNullOrEmpty ( equipmentCode ))
{
whereSql = $ @"where t1.EquipmentCode = '{equipmentCode}'" ;
}
赖素文
authored
11 months ago
442
443
sql = sql + @"
select COUNT (*) as alarmCount , CAST ( FORMAT ( t1 . CreateTime , ' HH ' ) AS INT ) as day_hour
444
from # tempdbRate t1 { 0 }
赖素文
authored
11 months ago
445
446
group by CAST ( FORMAT ( t1 . CreateTime , ' HH ' ) AS INT )
order by CAST ( FORMAT ( t1 . CreateTime , ' HH ' ) AS INT );
447
448
select * from # tempdbRate t1 { 0 }
赖素文
authored
11 months ago
449
450
select sumVal = sum ( ErrorDuration ) from # tempdbRate t1 { 0 } " ;
451
sql = string . Format ( sql , whereSql );
452
DataSet ds = Context . Ado . GetDataSetAll ( sql );
453
454
455
456
List < EquipmentAlarmCountTimeEntity > list = new List < EquipmentAlarmCountTimeEntity >();
var result = ds . Tables [ 1 ];
foreach ( DataRow dr in result . Rows )
457
{
458
459
460
461
462
463
464
465
466
467
int hour = ( int ) dr [ "day_hour" ];
EquipmentAlarmCountTimeEntity entity = new EquipmentAlarmCountTimeEntity ();
entity . AlarmCount = ( int ) dr [ "alarmCount" ];
entity . TimePeriod = hour . ToString ();
entity . AlarmListData = ds . Tables [ 2 ]. AsEnumerable (). Where ( x => x . Field < DateTime >( "CreateTime" ). Hour == hour ). CopyToDataTable ();
list . Add ( entity );
}
response . data = list ;
response . count = ds . Tables [ 2 ]. Rows . Count ;
468
response . message = list . Count == 0 ? " " : "故障次数天每小时对比,总次数:" + ds . Tables [ 2 ]. Rows . Count + ",总故障分钟数:" + ds . Tables [ 3 ]. Rows [ 0 ][ "sumVal" ]. ToString ();
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
return response ;
});
}
/// < summary >
/// 故障次数
/// </ summary >
/// < param name = "projectCode" ></ param >
/// < param name = "startTime" ></ param >
/// < param name = "endTime" ></ param >
/// < returns ></ returns >
public dynamic GetFailureAlarmCount ( string projectCode , DateTime startTime , DateTime endTime )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
var begin = startTime ;
var end = DateTime . Parse ( endTime . ToString ( "yyyy-MM-dd" ) + " 23:59:59" );
var data = Context . Queryable < daq_equipment_alarm_record , base_equipment_type >(( x , y ) => x . EquipmentTypeCode == y . code )
. Where (( x , y ) => (! SystemVariable . IotNotContainDevice . Contains ( x . EquipmentTypeCode )) && x . ProjectCode == projectCode && x . createTime >= begin && x . createTime <= end )
. Select (( x , y ) => new
{
x . equipmentCode ,
x . equipmentName ,
493
// errorduration = x . errorduration > 0 ? Math . Round ( x . errorduration / 60.0 , 2 ) : 0 ,
494
495
496
497
498
499
500
501
x . EquipmentTypeCode ,
EquipmentTypeName = y . name ,
x . alarmMessage ,
x . remark ,
x . createTime ,
x . updateTime
})
. ToList ();
502
// 设备故障分组
503
504
505
506
507
508
509
510
511
512
513
var res = data . GroupBy ( x => new { x . equipmentCode , x . equipmentName , x . EquipmentTypeName , x . alarmMessage })
. Select ( x => new
{
equipmentCode = x . Key . equipmentCode . ToString (),
equipmentName = x . Key . equipmentName . ToString (),
equipmentTypeName = x . Key . EquipmentTypeName ,
alarmMessage = x . Key . alarmMessage ,
alarmCount = x . Count ()
})
. OrderBy ( x => x . equipmentCode )
. ToList ();
514
515
516
517
518
519
520
// 故障次数分组
var alarmRes = data . GroupBy ( x => new { x . alarmMessage })
. Select ( x => new
{
alarmMessage = x . Key . alarmMessage ,
alarmCount = x . Count ()
})
521
. OrderByDescending ( x => x . alarmCount )
522
523
524
525
526
527
. ToList ();
response . data = new
{
res = res ,
alarmRes = alarmRes
};
528
response . count = res . Count ;
529
530
531
532
return response ;
});
}
533
/// < summary >
534
535
536
537
538
539
540
541
542
/// 查询项目列表
/// </ summary >
/// < param name = "agvTag" ></ param >
/// < returns ></ returns >
public dynamic GetProjects ( bool agvTag )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
赖素文
authored
9 months ago
543
var expression = GetProjectsWhere ();
544
var result = Context . Queryable < base_project >()
赖素文
authored
9 months ago
545
. Where ( expression )
546
. OrderBy ( x => x . createTime )
547
. Select ( x => new { x . projectCode , x . projectName , x . createTime })
548
. ToList ();
赖素文
authored
9 months ago
549
550
551
552
553
// agvTag 标记 true ,只查询含 agv 设备的项目
if ( agvTag )
{
赖素文
authored
9 months ago
554
var expressionAgv = GetProjectsAgvWhere ();
555
result = Context . Queryable < base_project , base_equipment >(( x , y ) => x . keys == y . projectKeys )
赖素文
authored
9 months ago
556
. Where ( expressionAgv )
557
558
. OrderBy (( x , y ) => x . createTime )
. Select (( x , y ) => new { x . projectCode , x . projectName , x . createTime })
559
560
561
. Distinct ()
. ToList ();
}
赖素文
authored
9 months ago
562
563
564
565
566
567
568
response . data = result ;
response . count = result . Count ;
return response ;
});
}
赖素文
authored
9 months ago
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
public Expression < Func < base_project , bool >> GetProjectsWhere ()
{
try
{
var exp = Expressionable . Create < base_project >();
// 非管理员,查询登陆用户绑定客户管理的项目
string currentUser = sysUserApiAccount ;
if ( currentUser != SystemVariable . DefaultCreated )
{
var projectRoleKeys = GetProjectRoleKeys ( currentUser );
exp . And ( x => SqlFunc . Subqueryable < sys_role_projects_rel >(). Where ( c => projectRoleKeys . Contains ( c . project_roles_key ) && c . project_key == x . keys ). Any ());
}
return exp . ToExpression ();// 拼接表达式
}
catch ( Exception ex )
{
throw new Exception ( $ "{ex.Message}" );
}
}
public Expression < Func < base_project , base_equipment , bool >> GetProjectsAgvWhere ()
{
try
{
var exp = Expressionable . Create < base_project , base_equipment >();
// 非管理员,查询登陆用户绑定客户管理的项目
string currentUser = sysUserApiAccount ;
if ( currentUser != SystemVariable . DefaultCreated )
{
var projectRoleKeys = GetProjectRoleKeys ( currentUser );
599
exp . And (( x , y ) => SqlFunc . Subqueryable < sys_role_projects_rel >(). Where ( c => projectRoleKeys . Contains ( c . project_roles_key ) && c . project_key == x . keys ). Any ());
赖素文
authored
9 months ago
600
601
602
603
604
605
606
607
608
}
exp . And (( x , y ) => SystemVariable . IotAGVContainDevice . Contains ( y . equipmentTypeCode ));
return exp . ToExpression ();// 拼接表达式
}
catch ( Exception ex )
{
throw new Exception ( $ "{ex.Message}" );
}
}
609
610
/// < summary >
611
612
613
614
615
/// 公共方法: 项目查询设备
/// </ summary >
/// < param name = "projectCode" ></ param >
/// < param name = "agvTag" ></ param >
/// < returns ></ returns >
616
public dynamic GetEquipmentsByProject ( string projectCode , bool agvTag )
617
618
619
620
621
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
var result = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
622
623
. Where (( x , y ) => y . projectCode == projectCode
&& ! SystemVariable . IotNotContainDevice . Contains ( x . equipmentTypeCode ))
624
. OrderBy (( x , y ) => x . equipmentCode )
625
. Select (( x , y ) => new { x . equipmentCode , x . equipmentName , x . equipmentTypeCode })
626
. ToList ();
627
628
629
// agvTag 标记 true ,只查询 agv 设备
if ( agvTag )
{
630
result = result . AsEnumerable (). Where ( x => SystemVariable . IotAGVContainDevice . Contains ( x . equipmentTypeCode )). ToList ();
631
632
}
633
634
635
636
637
response . data = result ;
response . count = result . Count ;
return response ;
});
}
638
639
640
641
642
/// < summary >
/// 公共方法: AGV 报警列表
/// </ summary >
/// < returns ></ returns >
643
public dynamic GetAGVAlarmList ( string projectCode , string equipmentCode )
644
645
646
647
648
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
var result = Context . Queryable < daq_equipment_alarm_record >()
649
. Where ( x => SystemVariable . IotAGVContainDevice . Contains ( x . EquipmentTypeCode ))
650
651
. Where ( x => string . IsNullOrEmpty ( projectCode ) || x . ProjectCode == projectCode )
. Where ( x => string . IsNullOrEmpty ( equipmentCode ) || x . equipmentCode == equipmentCode )
652
653
654
655
656
. Select ( x => new
{
key = x . alarmMessage ,
value = x . alarmMessage
})
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
. Distinct (). ToList ();
response . data = result ;
response . count = result . Count ;
return response ;
});
}
/// < summary >
/// AGV 设备异常分析(点位维度)
/// </ summary >
/// < returns ></ returns >
public dynamic GetAGVCodePointAlarmRecord ( string startTime , string endTime , string projectCode , string equipmentCode , string alarmMsg )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
DataSet ds = new DataSet ();
var startDate = startTime + " 00:00:00" ;
var endDate = endTime + " 23:59:59" ;
676
var sqlWhere = $ " and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')" ;
677
678
679
if (! string . IsNullOrEmpty ( projectCode )) sqlWhere += $ " and t1.ProjectCode = '{projectCode}' " ;
if (! string . IsNullOrEmpty ( equipmentCode )) sqlWhere += $ " and t1.EquipmentCode = '{equipmentCode}' " ;
if (! string . IsNullOrEmpty ( alarmMsg )) sqlWhere += $ " and t1.AlarmMessage = '{alarmMsg}' " ;
680
var sql = GetAlarmSql ( sqlWhere , startDate , endDate ) + GetAGVCodePointAlarmRecordSql ( projectCode , alarmMsg );
681
ds = Context . Ado . GetDataSetAll ( sql );
682
683
684
685
686
687
688
689
690
var resDt = ds . Tables [ 2 ];
// 查询项目所有设备
if (! string . IsNullOrEmpty ( projectCode ) && string . IsNullOrEmpty ( equipmentCode ))
{
var equipmentList = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
. Where (( x , y ) => y . projectCode == projectCode && x . equipmentTypeCode . Contains ( "AGV" ))
. Select (( x , y ) => new { x . equipmentCode , x . equipmentName , y . projectName })
. ToList ();
691
if ( resDt != null && resDt . Rows . Count > 0 )
692
693
694
695
696
697
698
699
700
701
702
703
{
foreach ( var i in equipmentList )
{
bool exists = resDt . AsEnumerable (). Any ( r => r . Field < string >( "EquipmentName" ) == i . equipmentName );
if (! exists )
{
resDt . Rows . Add ( 0 , i . equipmentCode , i . equipmentName , resDt . Rows [ 0 ][ "CodePoint" ]);
}
}
}
else
{
704
foreach ( var i in equipmentList )
705
706
707
708
{
resDt . Rows . Add ( 0 , i . equipmentCode , i . equipmentName , ds . Tables [ 1 ]. Rows [ 0 ][ "BarCode" ]);
}
}
709
710
711
}
712
713
714
response . data = new
{
codepoints = ds . Tables [ 1 ],// 该项目所有点位
715
result = resDt ,// 有报警的点位以及信息
716
};
717
response . count = resDt . Rows . Count ;
718
719
720
721
722
return response ;
});
}
/// < summary >
723
/// AGV 点位维度异常分析
724
725
726
/// </ summary >
/// < param name = "alarmMsg" ></ param >
/// < returns ></ returns >
727
public string GetAGVCodePointAlarmRecordSql ( string projectCode , string alarmMsg )
728
{
729
var sql = $ "select distinct ProjectCode,BarCode from daq_agv_point where ProjectCode='{projectCode}' order by BarCode;" ;
730
731
if (! string . IsNullOrEmpty ( alarmMsg ))
{
732
sql += @" select COUNT (*) as count ,
733
EquipmentCode , EquipmentName ,
734
735
736
737
738
CodePoint ,
AlarmMessage
from
# tempdbRate t
group by
739
EquipmentCode , EquipmentName ,
740
741
CodePoint ,
AlarmMessage " ;
742
743
744
}
else
{
745
sql += @" select COUNT (*) as count ,
746
EquipmentCode , EquipmentName ,
747
CodePoint
748
749
750
from
# tempdbRate t
group by
751
EquipmentCode , EquipmentName ,
752
753
754
755
756
757
758
CodePoint " ;
}
return sql ;
}
/// < summary >
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
/// AGV 设备异常分析(时间维度)
/// </ summary >
/// < param name = "startTime" ></ param >
/// < param name = "endTime" ></ param >
/// < param name = "projectCode" ></ param >
/// < param name = "equipmentCode" ></ param >
/// < param name = "alarmMsg" ></ param >
/// < returns ></ returns >
public dynamic GetAGVAbnormalRecord ( string startTime , string endTime , string projectCode , string equipmentCode , string alarmMsg )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
DataSet ds = new DataSet ();
var startDate = startTime + " 00:00:00" ;
var endDate = endTime + " 23:59:59" ;
var sqlWhere = $ " and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')" ;
if (! string . IsNullOrEmpty ( projectCode )) sqlWhere += $ " and t1.ProjectCode = '{projectCode}' " ;
if (! string . IsNullOrEmpty ( equipmentCode )) sqlWhere += $ " and t1.EquipmentCode = '{equipmentCode}' " ;
if (! string . IsNullOrEmpty ( alarmMsg )) sqlWhere += $ " and t1.AlarmMessage = '{alarmMsg}' " ;
var sql = GetAlarmSql ( sqlWhere , startDate , endDate ) + GetAGVAbnormalRecordSql ( alarmMsg );
ds = Context . Ado . GetDataSetAll ( sql );
781
782
783
var resDt = ds . Tables [ 1 ];
// 查询项目所有设备
784
if (! string . IsNullOrEmpty ( projectCode ) && string . IsNullOrEmpty ( equipmentCode ) && resDt . Rows . Count > 0 )
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
{
var equipmentList = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
. Where (( x , y ) => y . projectCode == projectCode && x . equipmentTypeCode . Contains ( "AGV" ))
. Select (( x , y ) => new { x . equipmentCode , x . equipmentName , y . projectName })
. ToList ();
foreach ( var i in equipmentList )
{
bool exists = resDt . AsEnumerable (). Any ( r => r . Field < string >( "EquipmentName" ) == i . equipmentName );
if (! exists )
{
resDt . Rows . Add ( 0 , i . equipmentCode , i . equipmentName , startTime );
}
}
}
response . data = resDt ;
801
802
803
804
805
806
807
808
809
810
811
response . count = ds . Tables [ 1 ]. Rows . Count ;
return response ;
});
}
public string GetAGVAbnormalRecordSql ( string alarmMsg )
{
var sql = "" ;
if (! string . IsNullOrEmpty ( alarmMsg ))
{
sql += @" select COUNT (*) as count ,
812
EquipmentCode , EquipmentName ,
813
814
815
816
817
CONVERT ( varchar , created , 23 ) as date ,
AlarmMessage
from
# tempdbRate t
group by
818
EquipmentCode , EquipmentName ,
819
820
821
822
823
824
CONVERT ( varchar , created , 23 ),
AlarmMessage " ;
}
else
{
sql += @" select COUNT (*) as count ,
825
EquipmentCode , EquipmentName ,
826
827
828
829
CONVERT ( varchar , created , 23 ) as date
from
# tempdbRate t
group by
830
EquipmentCode , EquipmentName ,
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
CONVERT ( varchar , created , 23 ) " ;
}
return sql ;
}
/// < summary >
/// AGV 故障任务妥善率
/// </ summary >
/// < param name = "startTime" ></ param >
/// < param name = "endTime" ></ param >
/// < param name = "yearOrdaySelectVal" ></ param >
/// < param name = "timeFlag" ></ param >
/// < param name = "projectCode" ></ param >
/// < param name = "equipmentCode" ></ param >
/// < returns ></ returns >
public dynamic GetAGVProperRate ( string startTime , string endTime , string yearOrdaySelectVal , string timeFlag , string projectCode , string equipmentCode )
{
var response = new ResponseNew ();
return ExceptionsHelp . Instance . ExecuteT (() =>
{
// 时间段筛选
if ( timeFlag == "year" )
{
startTime = yearOrdaySelectVal + "-01-01" ;
endTime = yearOrdaySelectVal + "-12-31" ;
}
else if ( timeFlag == "day" )
{
startTime = yearOrdaySelectVal ;
endTime = yearOrdaySelectVal ;
}
862
string startDate = startTime ;
863
864
865
866
867
868
startTime = startTime + " 00:00:00" ;
endTime = endTime + " 23:59:59" ;
// 故障次数
var sqlWhere = $ " and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')" ;
if (! string . IsNullOrEmpty ( projectCode )) sqlWhere += $ " and t1.ProjectCode = '{projectCode}' " ;
if (! string . IsNullOrEmpty ( equipmentCode )) sqlWhere += $ " and t1.EquipmentCode = '{equipmentCode}' " ;
869
var sql = GetAlarmSql ( sqlWhere , startTime , endTime ) + GetAGVProperRateSql ( timeFlag , startTime , endTime , projectCode );
870
var ds = Context . Ado . GetDataSetAll ( sql );
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
var resDt = ds . Tables [ 1 ];
// 查询项目所有设备
if (! string . IsNullOrEmpty ( projectCode ) && string . IsNullOrEmpty ( equipmentCode ))
{
var equipmentList = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
. Where (( x , y ) => y . projectCode == projectCode && x . equipmentTypeCode . Contains ( "AGV" ))
. Select (( x , y ) => new { x . equipmentCode , x . equipmentName , y . projectName })
. ToList ();
foreach ( var i in equipmentList )
{
bool exists = resDt . AsEnumerable (). Any ( r => r . Field < string >( "EquipmentName" ) == i . equipmentName );
if (! exists )
{
resDt . Rows . Add ( i . equipmentCode , i . equipmentName , startDate , 0 , 0 );
}
}
}
resDt . Columns . Add ( "Rate" , typeof ( string ));
foreach ( DataRow row in resDt . Rows )
892
{
893
if (( decimal ) row [ "TotalTask" ] == 0 )
894
895
896
897
898
899
900
901
902
{
row [ "Rate" ] = "100%" ;
}
else
{
decimal result = Math . Round (( int ) row [ "counts" ] / ( decimal ) row [ "TotalTask" ], 2 );
var rate = ( 1 - result ). ToString ( "P2" );
row [ "Rate" ] = rate ;
}
903
}
904
905
906
907
908
response . data = resDt ;
response . count = resDt . Rows . Count ;
909
910
911
912
return response ;
});
}
913
public string GetAGVProperRateSql ( string timeFlag , string startTime , string endTime , string projectCode )
914
{
915
var sql = @" IF OBJECT_ID ( ' Tempdb .. # tempdbAlarmCount ' ) IS NOT NULL
916
917
918
919
920
921
DROP TABLE # tempdbAlarmCount ;
SELECT *
INTO # tempdbAlarmCount
FROM
(
select COUNT (*) as counts ,
922
EquipmentCode , EquipmentName ,
923
924
925
{ 0 } as dateVal
from # tempdbRate
group by { 0 },
926
EquipmentCode , EquipmentName
927
928
929
930
931
932
933
934
) t ;
select
task .*,
temp . counts
from
# tempdbAlarmCount temp join (
select
935
t . EquipmentCode , t . EquipmentName ,
936
937
938
939
940
941
942
t . dateVal ,
sum ( d . TotalTask ) as TotalTask
from
# tempdbAlarmCount t join daq_equipment_task d
on t . EquipmentCode = d . EquipmentCode
and t . dateVal = { 0 }
group by
943
t . EquipmentCode , t . EquipmentName ,
944
945
t . dateVal
) as task on task . EquipmentCode = temp . EquipmentCode
946
947
and task . dateVal = temp . dateVal
order by task . EquipmentCode " ;
948
949
950
951
952
953
954
955
956
957
958
959
if ( timeFlag == "year" )
{
sql = string . Format ( sql , "CONVERT(varchar(7),created,120)" );
}
else
{
sql = string . Format ( sql , "CONVERT(varchar,created,23)" );
}
return sql ;
}
/// < summary >
960
961
962
963
/// 公共方法:查询对应条件的故障(过滤同设备同年月日时分的数据) 临时表 # tempdbRate
/// </ summary >
/// < param name = "sqlWhere" ></ param >
/// < returns ></ returns >
964
public string GetAlarmSql ( string sqlWhere , string begin , string end , Guid [] projectKeys = null )
965
966
967
968
969
970
971
972
973
974
975
976
977
{
string sql = @" ----------------- 数据源 --------------------------------------------
IF OBJECT_ID ( ' Tempdb .. # tempdbRate ' ) IS NOT NULL
DROP TABLE # tempdbRate ;
SELECT *
INTO # tempdbRate
FROM
(
SELECT t1 . CreateTime AS created ,
t1 . UpdateTime AS updated ,
t1 . CreateTime , t1 . UpdateTime ,
ErrorDuration = CAST ( t1 . ErrorDuration / 60.0 as DECIMAL ( 10 , 2 )),
t1 . EquipmentCode , t1 . EquipmentName , t1 . EquipmentPropName , t1 . Id AS EquipmentAlarmId ,
978
t1 . AlarmMessage , t1 . AlarmMessage + ';' + ISNULL ( t1 . Remark , '' ) AS Alarm , ISNULL ( t1 . Remark , '' ) AS Remark ,
979
t1 . ProjectCode , t1 . EquipmentTypeCode , p . projectName AS ProjectName ,
980
t . name AS EquipmentTypeName , t1 . Points as CodePoint , e . sequence ,
981
982
983
984
985
986
987
988
h . HandleInfo , h . HandleUser , h . Reason , t1 . HandleTime , t1 . UserHandleTime
FROM dbo . daq_equipment_alarm_record t1 with ( nolock )
LEFT JOIN dbo . bus_equipment_alarm_handle h
ON h . EquipmentAlarmRecordId = t1 . Id
LEFT JOIN dbo . base_equipment_type t
ON t . code = t1 . EquipmentTypeCode
LEFT JOIN dbo . base_project p
ON p . projectCode = t1 . ProjectCode
989
990
LEFT JOIN dbo . base_equipment e
ON e . equipmentCode = t1 . EquipmentCode
991
992
993
994
WHERE 1 = 1 { 0 }
AND t1 . CreateTime >= ' { 1 } '
AND t1 . CreateTime <= ' { 2 } '
AND t1 . EquipmentTypeCode not in ( ' { 3 } ' )
995
{ 4 }
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
) t ;
---------------- tempdbRate 处理相同设备在同年月日时分数据 >----------------
WITH CTE
AS ( SELECT ROW_NUMBER () OVER ( PARTITION BY e . EquipmentCode ,
SUBSTRING ( CONVERT ( VARCHAR ( 50 ), e . Created , 120 ), 1 , 16 ),
SUBSTRING ( CONVERT ( VARCHAR ( 50 ), e . updated , 120 ), 1 , 16 )
ORDER BY EquipmentCode
) AS rownumber ,
*
FROM # tempdbRate e )
DELETE FROM CTE WHERE rownumber > 1 ;
1008
SELECT * FROM # tempdbRate ORDER BY CreateTime desc ; " ;
1009
1010
1011
1012
1013
1014
1015
var wh = "" ;
if ( projectKeys != null )
{
wh = $ " and p.keys in('{string.Join(" ',' ", projectKeys)}')" ;
}
sql = string . Format ( sql , sqlWhere , begin , end , SystemVariable . IotNotContainDevice , wh );
1016
1017
return sql ;
}
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
//// 通用处理无数据返回设备信息
// private DataTable HandleDt ( DataTable resDt , string projectCode , string startDate )
//{
// DataTable eqDt = new DataTable ();
// if (! string . IsNullOrEmpty ( projectCode ))// 项目号不能为空,要查询对应的设备
// {
// eqDt = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
// . Where (( x , y ) => y . projectCode == projectCode )
// . Select (( x , y ) => new {
// date = startDate ,
// sumTime = 0 ,
// ProjectName = y . projectName ,
// x . equipmentCode ,
// EquipmentName = x . equipmentName
// }). ToDataTable ();
// foreach ( DataRow r in resDt . Rows )
// {
// string equipmentCode = r [ "equipmentCode" ]. ToString ();
// string equipmentName = r [ "EquipmentName" ]. ToString ();
// string projectName = r [ "ProjectName" ]. ToString ();
// string date = Convert . ToDateTime ( r [ "date" ]). ToString ( "yyyy-MM-dd" );
// decimal sumTime =( decimal ) r [ "sumTime" ];
// // 查找现有设备
// var eq = eqDt . AsEnumerable (). FirstOrDefault ( i =>
// i . Field < string >( "equipmentCode" ) == equipmentCode &&
// i . Field < string >( "date" ) == date );
// if ( eq != null )
// {
// // 存在,替换项
// eq [ "sumTime" ] = sumTime ;
// }
// else
// {
// // 不存在,追加新记录
// DataRow row = eqDt . NewRow ();
// row [ "equipmentCode" ] = equipmentCode ;
// row [ "EquipmentName" ] = equipmentName ;
// row [ "ProjectName" ] = projectName ;
// row [ "date" ] = date ;
// row [ "sumTime" ] = sumTime ;
// eqDt . Rows . Add ( row );
// }
// }
// }
// return eqDt ;
//}
private DataTable HandleDt ( DataTable resDt , string projectCode , string startDate )
1068
1069
1070
1071
1072
{
if (! string . IsNullOrEmpty ( projectCode ))
{
var equipmentList = Context . Queryable < base_equipment , base_project >(( x , y ) => x . projectKeys == y . keys )
. Where (( x , y ) => y . projectCode == projectCode )
1073
. Select (( x , y ) => new { x . equipmentCode , x . equipmentName , y . projectName , x . sequence })
1074
1075
1076
1077
1078
1079
. ToList ();
foreach ( var i in equipmentList )
{
bool exists = resDt . AsEnumerable (). Any ( r => r . Field < string >( "EquipmentName" ) == i . equipmentName );
if (! exists )
{
1080
resDt . Rows . Add ( startDate , 0 , i . projectName , i . equipmentCode , i . equipmentName , i . sequence );
1081
1082
1083
}
}
}
1084
1085
1086
DataView dv = new DataView ( resDt );
dv . Sort = "Sequence" ;
resDt = dv . ToTable ();
1087
1088
return resDt ;
}
1089
1090
}
}