MES / IOT · Files

using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;

using Hh.Mes.POJO.EnumEntitys;

using Hh.Mes.POJO.Response;

using Hh.Mes.POJO.ViewModel.Equipment;

using Hh.Mes.Service.Repository;
using SqlSugar;
using System;
using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

namespace Hh.Mes.Service.WebService.Equipment
{
    /// <summary>
    /// 设备可视化参数
    /// </summary>

    public class EquipmentVisualService : RepositorySqlSugar<base_equipment>

    {
        /// <summary>

        /// 设备健康表现、 操作手册、未处理设备报警记录

        /// </summary>

        public Response GetEquipmentFileList(base_equipment model)

        {
            var result = new Response();

            //开机时间以及当天各状态时间及占比（通用方法，考虑报表接口共用）
            var res = GetEquipmentHealth(model, DateTime.Today, true);
            var alarmRecords = res.alarmRecords;
            var alarmTotal = res.vm.AlarmTotal;

            result.Result = new
            {
                equipmentHealth = res.vm,
                fileList = res.dt.Tables[0],
                eqInfo = res.dt.Tables[1],
                alarmList = alarmRecords,
                alarmTotal,
            };
            result.Count = 1;
            return result;
        }

        public (EquipmentHealthVM vm, List<daq_equipment_alarm_record> alarmRecords, dynamic dt) GetEquipmentHealth(base_equipment model, DateTime day, bool isSelectAlarmList = false)
        {

            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($@" select * from sys_File
                                         where targetId=@targetId and targetTableName='base_equipment'
                                         union all
                                         select * from sys_File
                                         where targetId=@targetId2 and targetTableName='base_equipment_type';

                                        SELECT  t.EquipmentCode,
                                                ISNULL(DATEDIFF(second, t1.UpdateTime, GETDATE()), -1) AS seconds,

                                                t1.Status,                                               

	                                            t.ip,

	                                            simNumber=   (
                                                    SELECT STUFF((
                                                        SELECT ',' + CAST(rel.simNumber AS VARCHAR(4000))
                                                        FROM base_sim_equipment_rel rel
                                                        WHERE rel.EquipmentCode = t.EquipmentCode
                                                        FOR XML PATH('')
                                                    ), 1, 1, '')
                                                )  
                                         FROM base_equipment t

                                         LEFT JOIN daq_equipment_status_record t1 WITH(NOLOCK) ON t1.EquipmentCode = t.otherCode

                                         WHERE t.EquipmentCode=@EquipmentCode");

            var dt = Context.Ado.GetDataSetAll(stringBuilder.ToString(), new List<SugarParameter>(){

                      new SugarParameter("@targetId", model.equipmentCode),
                      new SugarParameter("@targetId2", model.equipmentTypeCode),
                      new SugarParameter("@EquipmentCode", model.equipmentCode),

                      new SugarParameter("@OtherEquipmentCode", model.otherCode),

            });

            //今天的记录

            var startTime = day;

            var records = Context.Queryable<daq_equipment_status_record_history>().Where(x => x.equipmentCode == model.otherCode && x.createTime >= startTime).Select(x => new

            {
                x.id,

                x.statusDuration,
                x.status

            }).ToList();

            var currentMonthStartTime = startTime.AddDays(1 - startTime.Day);

            //设备饼图当天 空闲时间

            var freeTimeSeconds = records.Where(x => x.status == EquipmentStatus.Free.ToString()).Sum(x => x.statusDuration);

            //设备饼图当天 运行时间

            var runTimeSeconds = records.Where(x => x.status == EquipmentStatus.Running.ToString()).Sum(x => x.statusDuration);

            //设备饼图当天 总故障时间
            var alarmTimeSeconds = Context.Queryable<daq_equipment_alarm_record>()

                                          .Where(x => x.equipmentCode == model.otherCode && x.createTime >= startTime)

                                          .Select(x => new

                                          {
                                              x.id,
                                              x.errorduration
                                          })

                                          .ToList()

                                          .Sum(x => x.errorduration);

            //设备饼图当天 总时间

            var dayTime = DateTime.Now;
            if (!startTime.Date.Equals(dayTime.Date)) dayTime = DateTime.Parse(startTime.ToString("yyyy-MM-dd") + " 23:59:59");
            var currentTotalSecondss = (dayTime - startTime).TotalSeconds;

            //设备饼图当天 离线时间

            var offlineTimeTicks = currentTotalSecondss - (alarmTimeSeconds + freeTimeSeconds + runTimeSeconds);

            if (offlineTimeTicks < 0)
            {
                offlineTimeTicks = 0;
            }
            var equipmentHealth = new EquipmentHealthVM
            {
                EquipmentStatus = new EquipmentStatusDto
                {

                    TodayPowerTime = TimeSpan.FromSeconds(records.Sum(x => x.statusDuration)).TotalSeconds,

                    CurrentMonthPowerTime = TimeSpan.FromSeconds(Context.Queryable<daq_equipment_status_record_history>()

                                                                        .Where(x => x.equipmentCode == model.otherCode && x.createTime >= currentMonthStartTime)

                                                                        .Sum(x => x.statusDuration)).TotalSeconds,

                    RunTime = runTimeSeconds,
                    FreeTime = freeTimeSeconds,
                    AlarmTime = alarmTimeSeconds,
                    OfflineTime = offlineTimeTicks,

                    RunProportion = Math.Round(runTimeSeconds * 100d / currentTotalSecondss, 1),
                    FreeProportion = Math.Round(freeTimeSeconds * 100d / currentTotalSecondss, 1),
                    AlarmProportion = Math.Round(alarmTimeSeconds * 100d / currentTotalSecondss, 1),
                    OfflineProportion = Math.Round(offlineTimeTicks * 100d / currentTotalSecondss, 1),

                }
            };

            //当前设备未处理的报警总数

            var alarmQuery = Context.Queryable<daq_equipment_alarm_record>().Where(x => x.equipmentCode == model.otherCode && !x.isHandled);

            var alarmTotal = alarmQuery.Count();

            var alarmRecords = new List<daq_equipment_alarm_record>();
            if (isSelectAlarmList)//设备可视化展示的数据

            {

                alarmRecords = alarmQuery.OrderBy(x => x.createTime, OrderByType.Desc).Take(200).ToList();
            }

            //保养提醒
            var maintainCount = Context.Queryable<bus_equipment_maintain_record_detail>().Where(x => x.equipmentCode == model.otherCode && x.status != 20).Count();

            equipmentHealth.AlarmTotal = alarmTotal;
            equipmentHealth.MaintainTotal = maintainCount;
            equipmentHealth.EquipmentCode = model.equipmentCode;
            equipmentHealth.EquipmentName = model.equipmentName;
            equipmentHealth.ProjectName = Context.Queryable<base_project>().Where(x => x.keys == model.projectKeys).Select(x => x.projectName).First();
            equipmentHealth.IP = model.ip;

            //设备状态赋值
            var statusText = "在线";
            var status = dt.Tables[1].Rows[0]["status"].ToString();
            int seconds = (int)dt.Tables[1].Rows[0]["seconds"];
            if (status == "Failure" || status == "Error")
            {
                statusText = "故障";
            }
            if (seconds > 300 || seconds == -1)
            {
                statusText = "离线";
            }
            else
            {
                statusText = "空闲";
            }
            equipmentHealth.Status = statusText;
            return (equipmentHealth, alarmRecords, dt);

        }

        /// <summary>
        /// 设备保养信息tab 概述总览数据、部件列表
        /// </summary>

        public Response GetEquipmentMaintainList(base_equipment model)
        {
            var result = new Response();
            var stringBuilder = new StringBuilder();
            //设备保养概述总表关联部件基础信息表

            stringBuilder.AppendLine(@" SELECT  t.EquipmentCode,
                                                t.LastMaintainTime,
                                                t.SumAmount,
                                                t1.Status,
                                                ISNULL(DATEDIFF(second, t1.UpdateTime, GETDATE()), -1) AS seconds,
                                                t.MaintainNumber,

                                                t.ToBeMaintainedNumber,                                               

	                                            t3.ip,
                                                t3.imgSrc

                                            FROM
                                            bus_equipment_maintain_record_top t
                                            LEFT JOIN daq_equipment_status_record t1 WITH(NOLOCK) ON t1.EquipmentCode = t.EquipmentCode

                                            LEFT JOIN base_equipment t3 WITH(NOLOCK) ON t3.equipmentCode = t.EquipmentCode
                                        WHERE t.EquipmentCode=@equipmentCode;

                                        select  p.*

                                                from bus_equipment_maintain_record_head p 
                                        where p.EquipmentCode=@equipmentCode ");
            var dt = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), new List<SugarParameter>(){

                      new SugarParameter("@equipmentCode", model.equipmentCode),
            });

            if (dt == null || dt.Tables[0].Rows.Count == 0) return result.ResponseError(SystemVariable.queryNotData);
            result.Result = new
            {
                main = dt.Tables[0],
                eqmaintainHeadList = dt.Tables[1]
            };

            return result;
        }

        /// <summary>

        ///  设备保养信息tab 已保养设备部件 未保养设备部件

        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Response GetEquipmentMaintainRecordList(string eqCode, string partCode)
        {
            var result = new Response();
            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine(@"SELECT h.EquipmentCode,
                                              h.PartCode,
                                              h.PartName,
                                              h.LastMaintainTime,
                                              h.NextMaintainTime,
                                              d.MaintainTime,
                                              d.Maintainer,
                                              d.MaintainContent,
                                              d.MaintainPrice,
                                              d.PushStatus,
                                              d.Remark,

                                              url=STUFF(( SELECT ',' + f.url  FROM sys_File f
                                                           WHERE targetTableName='bus_equipment_maintain_record_detail' and f.targetId = d.id  
                                                          FOR XML PATH('')   
		                                              ), 1, 1, '') 
                                        FROM dbo.bus_equipment_maintain_record_detail d LEFT
                                        JOIN dbo.bus_equipment_maintain_record_head h
                                        ON h.HeadKeys=d.HeadKeys
                                        WHERE h.EquipmentCode=@equipmentCode  AND d.Status=@status");

            if (!string.IsNullOrEmpty(partCode))
            {
                //非空 附加部件条件
                stringBuilder.AppendLine(" AND h.PartCode=@partCode");
            }

            //已保养
            var dt = base.Context.Ado.GetDataTable(stringBuilder.ToString(), new List<SugarParameter>(){
                      new SugarParameter("@equipmentCode", eqCode),
                      new SugarParameter("@partCode", partCode),

                      new SugarParameter("@status", (int)EnumMaintainStatus.Maintained),

            });

            //待保养
            var dt_tobe = base.Context.Ado.GetDataTable(stringBuilder.ToString(), new List<SugarParameter>(){
                      new SugarParameter("@equipmentCode", eqCode),
                      new SugarParameter("@partCode", partCode),

                      new SugarParameter("@status", (int)EnumMaintainStatus.ToBeMaintain),

            });
            result.Result = new
            {
                maintainList = dt,
                tobemaintainList = dt_tobe
            };
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="comparisonValue">比较值</param>
        /// <returns></returns>
        private long ConvertToTicks(DateTime startTime, DateTime endTime, DateTime comparisonValue)
        {
            var tmep = startTime;
            if (startTime < comparisonValue)
            {
                tmep = comparisonValue;
            }
            return (endTime - tmep).Ticks;
        }

    }
}