Blame view

sys/Hh.Mes.Service/ApiService/BulletinBoardService.cs 52.7 KB
1
2
using Hh.Mes.Common.DataTableTo;
using Hh.Mes.Common.log;
赖素文 authored
3
using Hh.Mes.Pojo.System;
4
using Hh.Mes.POJO.ApiEntity;
赖素文 authored
5
6
7
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
8
using Newtonsoft.Json;
赖素文 authored
9
using NPOI.POIFS.FileSystem;
10
using NPOI.SS.Formula.Functions;
11
using SqlSugar;
12
using SqlSugar.Extensions;
赖素文 authored
13
14
using System;
using System.Collections.Generic;
15
16
using System.Data;
using System.Linq;
赖素文 authored
17
using System.Linq.Expressions;
18
using System.Security.Claims;
赖素文 authored
19
using System.Text;
20
using static Microsoft.AspNetCore.Hosting.Internal.HostingApplication;
赖素文 authored
21
22
23
24
25
26

namespace Hh.Mes.Service
{
    public class BulletinBoardService : RepositorySqlSugar<sys_user>
    {
        /// <summary>
27
        /// 故障报告
赖素文 authored
28
        /// </summary>
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)
赖素文 authored
34
        {
35
            var response = new ResponseNew();
赖素文 authored
36
37
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
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
赖素文 authored
67
                {
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],
80
81
                    isInterval = isInterval,
82
                    projectAlarmSum = dss.Item1.Tables[6],
83
                    projectAlarmSumInfo = dss.Item1.Tables[7]
赖素文 authored
84
85
86
                };
                return response;
            });
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";
94
            var sqlAlarm40 = "";
95
            if (!string.IsNullOrEmpty(projectCode))
96
97
98
99
            {
                var whereSql = $" WHERE t.projectCode='{projectCode}'";
                sqlMonth += whereSql;
                sqlWeek += whereSql;
100
                sqlAlarm40 = $" and t.projectCode='{projectCode}'";
101
102
103
104
105
            }

            var groupBySql = " group by projectCode,projectName order by sumTime desc";
            sqlMonth += groupBySql;
            sqlWeek += groupBySql;
106
            var projectKeys = base.GetProjectInfoKeys(base.sysUserApiAccount);
赖素文 authored
107
            var sql = @$"  -----------------本月公共数据源 ErrorDuration 已转化分)--------------------------------------------
108
109
110
                         IF OBJECT_ID('Tempdb..#listMonth') IS NOT NULL
                         DROP TABLE #listMonth;
                         SELECT * INTO #listMonth FROM (
赖素文 authored
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,
114
                                            t1.UpdateTime AS Updated,
115
                                            ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)) ,
116
                                            t1.EquipmentCode,
117
                                            t1.AlarmMessage + ';' + ISNULL(t1.Remark,'') AS Alarm,
118
119
120
121
                                            t1.EquipmentName,
                                            t1.EquipmentPropName,
                                            t1.EquipmentTypeCode,
                                            t1.ProjectCode,
赖素文 authored
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}
126
                                   and p.keys in('{string.Join("','", projectKeys)}')
127
128
                          )t;
                         ----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
赖素文 authored
129
                         DELETE FROM #listMonth   WHERE rownumber > 1;
130
赖素文 authored
131
                         -----------------本周公共数据源  #listWeek  ErrorDuration 已转化分--------------------------------------------
132
133
134
135
136
137
                        IF OBJECT_ID('Tempdb..#listWeek') IS NOT NULL
                        DROP TABLE #listWeek;
                        SELECT *
                        INTO #listWeek
                        FROM
                        (
赖素文 authored
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,
141
                                   t1.UpdateTime AS updated,
142
                                   ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)),
143
144
145
146
                                   t1.EquipmentCode,
                                   t1.EquipmentName,
                                   t1.EquipmentPropName,
                                   t1.Id AS EquipmentAlarmId,
147
                                   t1.AlarmMessage+ ';'  + ISNULL(t1.Remark,'') AS Alarm,
148
149
150
                                   t1.ProjectCode,
                                   t1.EquipmentTypeCode,
                                   p.projectName AS ProjectName
151
                            FROM dbo.daq_equipment_alarm_record t1 with(nolock)
赖素文 authored
152
153
                            LEFT JOIN dbo.base_project p  ON p.projectCode = t1.ProjectCode
                           {sqlWhereWeek} 
154
                           and p.keys in('{string.Join("','", projectKeys)}')
155
156
157
                        ) t;

                        ----------------listWeek 处理相同设备在同年月日时分数据>----------------
赖素文 authored
158
                       DELETE FROM #listWeek  WHERE rownumber > 1;
159
160
161
162
163
164
165
166
167

                        ----------------故障時間本月开始listMonth0 表格1-------------------------------
                        {sqlMonth} 
                        ----------------故障時間 月初、月末结束-------------------------------

                        ----------------故障時間本周开始listWeek1 表格1-------------------------------
                        {sqlWeek}
                         ----------------故障時間本周结束-------------------------------
赖素文 authored
168
                        --------------本周故障時間列表超過40分开始 ----------------
169
170
171
172
173
                        SELECT t.*,
                               eh.HandleInfo,
                               eh.HandleUser,
                               eh.Reason
                        FROM #listweek t
赖素文 authored
174
                        LEFT JOIN bus_equipment_alarm_handle eh ON eh.EquipmentAlarmRecordId = t.EquipmentAlarmId
175
                        WHERE t.ErrorDuration > 40  {sqlAlarm40}
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'
187
188

                        --项目设备报警统计
189
					   select   t.ProjectCode, xAxis =convert(varchar(10),created,120),sumVal= SUM(t.ErrorDuration)  from #listWeek t
190
					   group by  t.ProjectCode,convert(varchar(10),created,120) 
191
192
					   --项目设备报警统计明细信息
赖素文 authored
193
					   select xAxis =convert(varchar(10),created,120),timeVal=ErrorDuration,*  from #listWeek ";
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)
202
203
204
205
206
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var whereSql = "";
207
                if (!string.IsNullOrEmpty(projectCode)) whereSql = $"and t1.projectCode='{projectCode}'";
208
                if (!string.IsNullOrEmpty(equipmentCode)) whereSql = $" and t1.equipmentCode='{equipmentCode}'";
赖素文 authored
209
210
211

                var projectKeys = base.GetProjectInfoKeys(base.sysUserApiAccount);
                var sql = GetAlarmSql(whereSql, begin, end, projectKeys);
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">值有yearmonthday</param>
        /// <returns></returns>
230
        public dynamic GetProperRate(string startTime, string endTime, string yearOrdaySelectVal, string timeFlag, string projectCode, string equipmentCode)
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))
248
                {
249
                    whereSql = $"and t.projectCode='{projectCode}' and t.EquipmentCode='{equipmentCode}'";
250
                }
251
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentCode))
252
                {
253
                    whereSql = $"and t.projectCode='{projectCode}'";
254
                }
255
                if (string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentCode))
256
                {
257
                    whereSql = $"and t.EquipmentCode='{equipmentCode}'";
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);
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;
276
277
278
279
                return response;
            }, catchRetrunValue: "list");
        }
280
        public string GetRateSql(string timeFlag, string whereSql)
281
        {
282
            var sql = @"select {0} as date,
283
                                sumTime = sum(ErrorDuration),
284
                                ProjectName,EquipmentCode,EquipmentName,Sequence
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")
            {
HuXiYu authored
292
                sql = string.Format(sql, "CONVERT(varchar(7),created,120)", whereSql);//年月分组
293
294
295
            }
            else
            {
HuXiYu authored
296
                sql = string.Format(sql, "CONVERT(varchar,created,23)", whereSql);//年月日分组
297
            }
298
            return sql;
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);
342
                //处理没有数据的赋值0
343
                var dt = ds.Tables[1];
344
                var alarmDt = dt.AsEnumerable().GroupBy(x => x["Alarm"]).Distinct().Select(x => x.Key).ToList();
345
346
                var sumTime = dt.GetColumnValues<decimal>("SumTime").Sum();
                var sumMsgInfo = "故障月份对比 ";
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");
351
352
                    var totalSumTime = dt.CalculateSumTime("YearMonth", "SumTime", currentTime);
                    sumMsgInfo += currentTime + "故障分钟:" + totalSumTime + ",";
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,
367
                    allAlarm = ds.Tables[0],
368
                    sumMsgInfo = sumMsgInfo + " 总故障时间:" + sumTime + "(单位分钟)"
369
                };
370
371
372
                response.count = dt.Rows.Count;
                return response;
            }, catchRetrunValue: "list");
赖素文 authored
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;
HuXiYu authored
411
                    entity.AlarmListData = dt.Count > 0 ? dt.CopyToDataTable() : null;
412
413
                    list.Add(entity);
                }
414
                response.count = allAlarmDt.Rows.Count;
HuXiYu authored
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}'";
                }
442
443
                sql = sql + @"
                            select COUNT(*) as alarmCount, CAST(FORMAT(t1.CreateTime , 'HH') AS INT) as day_hour 
444
                            from #tempdbRate t1 {0}
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}
449
450
                            select sumVal= sum(ErrorDuration) from  #tempdbRate t1 {0} ";
451
                sql = string.Format(sql, whereSql);
452
                DataSet ds = Context.Ado.GetDataSetAll(sql);
HuXiYu authored
453
454
455
456

                List<EquipmentAlarmCountTimeEntity> list = new List<EquipmentAlarmCountTimeEntity>();
                var result = ds.Tables[1];
                foreach (DataRow dr in result.Rows)
457
                {
HuXiYu authored
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();
HuXiYu authored
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,
HuXiYu authored
494
495
496
497
498
499
500
501
                                      x.EquipmentTypeCode,
                                      EquipmentTypeName = y.name,
                                      x.alarmMessage,
                                      x.remark,
                                      x.createTime,
                                      x.updateTime
                                  })
                                  .ToList();
502
                //设备故障分组
HuXiYu authored
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()
                              })
HuXiYu authored
521
                              .OrderByDescending(x => x.alarmCount)
522
523
524
525
526
527
                              .ToList();
                response.data = new
                {
                    res = res,
                    alarmRes = alarmRes
                };
HuXiYu authored
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
543
                var expression = GetProjectsWhere();
544
                var result = Context.Queryable<base_project>()
赖素文 authored
545
                                    .Where(expression)
546
                                    .OrderBy(x => x.createTime)
HuXiYu authored
547
                                    .Select(x => new { x.projectCode, x.projectName, x.createTime })
548
                                    .ToList();
赖素文 authored
549
550
551
552
553
                //agvTag标记true,只查询含agv设备的项目
                if (agvTag)
                {
赖素文 authored
554
                    var expressionAgv = GetProjectsAgvWhere();
HuXiYu authored
555
                    result = Context.Queryable<base_project, base_equipment>((x, y) => x.keys == y.projectKeys)
赖素文 authored
556
                                    .Where(expressionAgv)
HuXiYu authored
557
558
                                    .OrderBy((x, y) => x.createTime)
                                    .Select((x, y) => new { x.projectCode, x.projectName, x.createTime })
559
560
561
                                    .Distinct()
                                    .ToList();
                }
赖素文 authored
562
563
564
565
566
567
568
                response.data = result;
                response.count = result.Count;
                return response;
            });
        }
赖素文 authored
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
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)
HuXiYu authored
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))
HuXiYu authored
624
                                    .OrderBy((x, y) => x.equipmentCode)
HuXiYu authored
625
                                    .Select((x, y) => new { x.equipmentCode, x.equipmentName, x.equipmentTypeCode })
HuXiYu authored
626
                                    .ToList();
627
628
629
                //agvTag标记true,只查询agv设备
                if (agvTag)
                {
630
                    result = result.AsEnumerable().Where(x => SystemVariable.IotAGVContainDevice.Contains(x.equipmentTypeCode)).ToList();
631
632
                }
HuXiYu authored
633
634
635
636
637
                response.data = result;
                response.count = result.Count;
                return response;
            });
        }
638
639
640
641
642

        /// <summary>
        /// 公共方法:AGV报警列表
        /// </summary>
        /// <returns></returns>
HuXiYu authored
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))
HuXiYu authored
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}' ";
HuXiYu authored
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>
HuXiYu authored
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}' ";
HuXiYu authored
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
                {
HuXiYu authored
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;
            });
        }
HuXiYu authored
913
        public string GetAGVProperRateSql(string timeFlag, string startTime, string endTime, string projectCode)
914
        {
HuXiYu authored
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;
        }
赖素文 authored
1089
1090
    }
}