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 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 * from daq_equipment_alarm_record t1  with (nolock)

                                        where t1.IsHandled =0 and t1.EquipmentCode=@EquipmentCode

                                        order by  t1.CreateTime desc

                                        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.EquipmentCode
                                         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),
            });

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

            {
                x.id,

                x.statusDuration,
                x.status

            }).ToList();

            var currentMonthStartTime = DateTime.Today.AddDays(1 - DateTime.Today.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.equipmentCode && x.createTime >= DateTime.Today)
                                          .Select(x => new
                                            {
                                                x.id,
                                                x.errorduration
                                            })
                                          .ToList()
                                          .Sum(x=>x.errorduration);

            //设备饼图当天 总时间

            var currentTotalSecondss = (DateTime.Now - DateTime.Today).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.equipmentCode &&  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),

                }
            };
            result.Result = new
            {
                equipmentHealth,

                fileList = dt.Tables[0],
                alarmList = dt.Tables[1],

                eqInfo = dt.Tables[2],

            };
            result.Count = 1;

            return result;
        }

        /// <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
                                            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.partCode, p.partName, rd.indicator,
                                                rd.indicatorType, rd.price, t3.dictLabel
                                                from bus_equipment_maintain_record_head p 
                                                left join bus_equipment_maintain_rule_detail rd on p.partCode=rd.equipmentPartCode
                                                left join  sys_dict_data t3 on rd.indicatorType=t3.dictValue and t3.dictType='IndicatorType'
                                        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.EquipmentTypeCode,
                                            h.PartCode,
                                            h.PartName,
                                            h.LastMaintainTime,
                                            h.NextMaintainTime,
                                            d.MaintainTime,
                                            d.Maintainer,
                                            d.MaintainContent,
                                            d.MaintainPrice,
                                            d.PushStatus,
                                            d.Attachment,
                                            d.Remark
                                        FROM dbo.bus_equipment_maintain_record_head h LEFT
                                        JOIN dbo.bus_equipment_maintain_record_detail d
                                        ON h.Keys=d.HeadKeys
                                        WHERE h.EquipmentCode=@equipmentCode  AND h.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", 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", 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;
        }

    }
}