ECS / ECS_EquipmentMangaSystem_BingChengTaiGuang · Files

using FreeSql;
using Google.Protobuf.WellKnownTypes;
using Hh.Mes.Service.WebService.Planned;
using HHECS.Application;
using HHECS.Application.ApiModel;
using HHECS.Application.Service;
using HHECS.Dal;
using HHECS.Dal.Repository;
using HHECS.Infrastructure.CommonHelper;
using HHECS.Infrastructure.Json;
using HHECS.Infrastructure.LogHelper;
using HHECS.Model.Entities;
using HHECS.Model.Enums;
using HHECS.WebAPI.Service.Interface;
using HHECS.WebAPI.ViewModel;
using MySqlX.XDevAPI.Common;
using Newtonsoft.Json;
using NPOI.HSSF.Record;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Reflection;

namespace HHECS.WebAPI.Service
{
    /// <summary>
    /// 看板接口数据服务
    /// </summary>
    public class DashboardService : BaseService, IDashboardService
    {
        private readonly EquipmentService _equipmentService = new EquipmentService();

        private static DateTime dt = DateTime.Now.Date;
        private static DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本周周一
        private DateTime endWeek = startWeek.AddDays(6); //本周周日
        private static DateTime startMonth = dt.AddDays(1 - dt.Day); //本月月初
        private DateTime endMonth = startMonth.AddMonths(1).AddDays(-1); //本月月末 //DateTime endMonth =


        public EquipmentMaintainViewModel GetEquipmentMaintain()
        {
            var result = new EquipmentMaintainViewModel();
            try
            {
                var EquipmentList = _equipmentService.GetEquipments(x => true).Data;
                var equipment = EquipmentList.OrderBy(x => Guid.NewGuid()).First();
                //保养规则
                using EquipmentMaintainRuleRepository maintainRuleRepository = new EquipmentMaintainRuleRepository();
                var equipmentMaintenanceRules = maintainRuleRepository.Where(x => x.EquipmentTypeId.Equals(equipment.EquipmentTypeId)).Include(x => x.EquipmentType).First();
                using EquipmentMaintainRuleDetailRepository maintainRuleDetailRepository = new EquipmentMaintainRuleDetailRepository();
                var maintenanceRules = maintainRuleDetailRepository.Where(x => x.EquipmentMaintainRuleId.Equals(equipmentMaintenanceRules.Id)).Include(t => t.EquipmentTypePart).ToList()
                    .Select(x => new MaintenanceRulesModel
                    {
                        PartName = x.EquipmentTypePart.PartName,
                        Indicator = $"{x.Indicator}",
                        IndicatorType = EnumHelper.GetDescription<IndicatorTypeEnums>($"{(int)(IndicatorTypeEnums)x.IndicatorType}"),
                        OneMonth = ExistsMonth(x.Indicator, x.IndicatorType, 1),
                        ThreeMonth = ExistsMonth(x.Indicator, x.IndicatorType, 3),
                        SixMonth = ExistsMonth(x.Indicator, x.IndicatorType, 6),
                        TwelveMonth = ExistsMonth(x.Indicator, x.IndicatorType, 12),
                        Dust = ExistsMonth(x.Indicator, x.IndicatorType),
                        Days = GetDay(equipment.Code, x.EquipmentTypePart.PartCode),
                        PartPrice = (int?)x.EquipmentTypePart.Price,
                        MaintenancePrice = x.Price,
                    }).ToList();

                //保养记录
                using EquipmentMaintainIndicatorHistoryRepository indicatorHistoryRepository = new EquipmentMaintainIndicatorHistoryRepository();
                var maintenanceRecordsData = indicatorHistoryRepository.Where(x => x.EquipmentCode == equipment.Code).ToList();
                var okMaintenanceCount = maintenanceRecordsData.Count(x => x.Status == EquipmentMaintainStatus.Maintained.GetIndexInt());
                var maintenanceRecords = maintenanceRecordsData.Select(x => new MaintenanceRecordsModel
                {
                    EquipmentCode = x.EquipmentCode,
                    Identification = x.Indicator,
                    StatusCode = x.Status,
                    PartName = x.PartName,
                    EquipmentType = equipment.EquipmentType.Name,
                    UsageTime = DateTime.Now - equipment.ProdTime,
                    TaskTotal = okMaintenanceCount,
                    RunningDistance = maintenanceRecordsData.Count - okMaintenanceCount,
                    MaintenanceTime = x.MaintainTime,
                    User = x.Maintainer,
                    LastMaintenanceTime = x.LastMaintainTime
                }).ToList();

                //待保养数据
                using EquipmentMaintainRecordRepository maintainRecordRepository = new EquipmentMaintainRecordRepository();
                var maintenanceResult = maintainRecordRepository.Where(x => x.EquipmentCode.Equals(equipment.Code) && x.Status != (int)EquipmentMaintainStatus.Maintained).ToList();

                //保养提醒
                var maintenanceReminder = maintenanceResult.Select(x => new MaintenanceReminderModel
                {
                    EquipmentCode = x.EquipmentCode,
                    EquipmentType = equipment.EquipmentType.Name,
                    PartName = x.PartName,
                    Days = (x.MaintainTime != null ? (x.MaintainTime - DateTime.Now).Value.Days : 0).ToString(),
                    Warning = x.Warning
                }).ToList();

                //预知保养
                var predictiveMaintenance = maintenanceResult.Select(x => new PredictiveMaintenanceModel
                {
                    EquipmentCode = x.EquipmentCode,
                    PartName = x.PartName,
                    Content = x.MaintenanceContent,
                    Days = (x.MaintainTime != null ? (x.MaintainTime - DateTime.Now).Value.Days : 0).ToString(),
                    Price = x.Price,
                }).ToList();


                result.EquipmentStatus = new EquipmentStatusModel
                {
                    EquipmentCode = equipment.Code,
                    EquipmentName = equipment.Name,
                    RunningDistance = okMaintenanceCount,
                    TaskTotal = maintenanceRecordsData.Count - okMaintenanceCount,
                    MaintenanceTime = maintenanceResult.FirstOrDefault()?.MaintainTime,
                    LastMaintenanceTime = maintenanceResult.FirstOrDefault()?.LastMaintainTime,
                };
                result.MaintenanceRecords = maintenanceRecords;
                result.MaintenanceReminder = maintenanceReminder;
                result.PredictiveMaintenance = predictiveMaintenance;
                result.MaintenanceRules = maintenanceRules;
                return result;
            }
            catch (Exception ex)
            {
                Log4NetHelper.Instance.Error($"获取设备保养看板数据出现异常、{ex.Message}");
            }
            return result;

        }

        public EquipmentRunStatusViewModel GetEquipmentRunStatus()
        {

            var result = new EquipmentRunStatusViewModel();
            try
            {
                using EquipmentStatusRecordRepository statusRecordRepository = new EquipmentStatusRecordRepository();
                var equipmentStatus = statusRecordRepository.Where(t => t.Created.Value > startMonth && t.Created.Value < endMonth).ToList();
                var codes = equipmentStatus.Select(t => t.EquipmentCode).Distinct().ToList();

                result.RunInfo = new EquipmentRunInfo
                {
                    Run = equipmentStatus.Count(x => x.EquipmentStatus == EquipmentStatusRecordStatus.Running.ToString()),
                    Standby = equipmentStatus.Count(x => x.EquipmentStatus == EquipmentStatusRecordStatus.Manual.ToString()),
                    Free = equipmentStatus.Count(x => x.EquipmentStatus == EquipmentStatusRecordStatus.Free.ToString()),
                    Error = equipmentStatus.Count(x => x.EquipmentStatus == EquipmentStatusRecordStatus.Error.ToString())
                };
                result.FailureRates = GetEquipmentMTBF(codes);
                result.UtilizationRate = EquipmentOEE(equipmentStatus.Where(x => x.EquipmentStatus == EquipmentStatusRecordStatus.Running.ToString()).ToList(), codes);
            }
            catch (Exception ex)
            {
                Log4NetHelper.Instance.Error($"设备运行信息看板接口出现异常、{ex.Message}");
            }
            return result;

        }

        public EquipmentAlarmViewModel GetEquipmentAlarmResult()
        {
            var result = new EquipmentAlarmViewModel();
            try
            {
                using EquipmentAlarmRecordRepository alarmRecordRepository = new EquipmentAlarmRecordRepository();
                var equipmentAlarmRecords = alarmRecordRepository.Where(t => t.Created.Value > startMonth && t.Created.Value < endMonth).ToList();
                var codes = equipmentAlarmRecords.Select(t => t.EquipmentCode).Distinct().ToList();
                var msg = equipmentAlarmRecords.Where(t => !t.IsEnd).Select(x => $"[{x.EquipmentCode}]{x.Alarm}").ToList();
                result.EquipmentFailureRates = GetEquipmentMTBF(equipmentAlarmRecords, codes);
                result.EquipmentAvailabilities = EquipmentOEE(codes);
                result.AlarmMsg = msg;
                return result;
            }
            catch (Exception ex)
            {
                Log4NetHelper.Instance.Error($"获取设备报警信息出现异常、{ex.Message}");
            }
            return result;
        }

        public EquipmentUtilizationViewModel GetEquipmentUtilization()
        {
            var result = new EquipmentUtilizationViewModel();
            try
            {
                using EquipmentAlarmRecordRepository equipmentAlarmRecord = new EquipmentAlarmRecordRepository();
                using EquipmentAlarmHandleRecordRepository equipmentAlarmHandleRecord = new EquipmentAlarmHandleRecordRepository();
                //最近50条报警信息
                var alarms = equipmentAlarmRecord.Where(x => true).OrderByDescending(x => x.Created).Take(50).ToList();
                //设备故障信息[历史记录]
                List<EquipmentFailureRateInfoModel> equipmentFailureRateInfo = alarms.Select(x => new EquipmentFailureRateInfoModel
                {
                    EquipmentCode = x.EquipmentCode,
                    Alarm = x.Alarm,
                    CreateTime = x.Created,
                    Duration = string.Format("{0:dd}天 {0:hh}小时 {0:mm}分 {0:ss}秒", x.Updated != null ? x.Updated - x.Created : DateTime.Now - x.Created),
                    User = equipmentAlarmHandleRecord.Where(t => t.EquipmentAlarmRecordId == x.Id).First()?.HandleUser
                }).ToList();

                //工作时间/运行时间 x 100%
                result.Utilization = EquipmentOEE();
                result.FailureRate = new EquipmentFailureRate
                {
                    FailureRateChart = GetEquipmentMTBF_Pie().ToList(),
                    FailureRateInfo = equipmentFailureRateInfo
                };
                //完好设备总台数/生产设备总台数 x 100%
                result.Availability = GetEquipmentMTBF();
            }
            catch (Exception ex)
            {
                Log4NetHelper.Instance.Error($"获取设备利用率出现异常、{ex.Message}");
            }
            return result;
        }

        /// <summary>
        /// 获取设备妥善率、故障率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel GetEquipmentMTBF(List<string> codes)
        {
            var result = new ChartsModel();
            var MTBFResult = _equipmentService.EquipmentMTBFPerformance(startMonth, endMonth, codes).ToList();
            var yData = new List<dynamic>();
            foreach (var eqCode in codes)
            {
                var mtbfItem = MTBFResult.Find(x => x.EquipmentCode == eqCode);
                if (mtbfItem == null)
                {
                    yData.Add(0);
                    continue;
                }
                var mtbf = decimal.Round((decimal)mtbfItem.MTBF / 60 / 60, 2);
                yData.Add(mtbf);
            }
            result.XData = codes;
            result.YData = yData;
            return result;
        }


        /// <summary>
        /// 获取设备妥善率、故障率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel GetEquipmentMTBF(List<EquipmentAlarmRecord> equipmentAlarmRecords, List<string> codes)
        {
            var result = new ChartsModel();
            var MTBFResult = _equipmentService.EquipmentMTBFPerformance(startMonth, endMonth, equipmentAlarmRecords, codes).ToList();
            var yData = new List<dynamic>();
            foreach (var eqCode in codes)
            {
                var mtbfItem = MTBFResult.Find(x => x.EquipmentCode == eqCode);
                if (mtbfItem == null)
                {
                    yData.Add(0);
                    continue;
                }
                var mtbf = decimal.Round((decimal)mtbfItem.MTBF / 60 / 60, 2);
                yData.Add(mtbf);
            }
            result.XData = codes;
            result.YData = yData;
            return result;
        }

        /// <summary>
        /// 获取设备妥善率、故障率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel GetEquipmentMTBF()
        {
            var result = new ChartsModel();
            var MTBFResult = _equipmentService.EquipmentMTBFPerformance(startMonth, endMonth).ToList();
            var yData = new List<dynamic>();
            var eqCodes = MTBFResult.Select(t => t.EquipmentCode).ToList();
            foreach (var eqCode in eqCodes)
            {
                var mtbfItem = MTBFResult.Find(x => x.EquipmentCode == eqCode);
                if (mtbfItem == null)
                {
                    yData.Add(0);
                    continue;
                }
                var mtbf = decimal.Round((decimal)mtbfItem.MTBF / 60 / 60, 2);
                yData.Add(mtbf);
            }
            result.XData = eqCodes;
            result.YData = yData;
            return result;
        }

        /// <summary>
        /// 获取设备利用率、稼动率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel EquipmentOEE(List<string> codes)
        {
            var OeeResult = _equipmentService.EquipmentOEEPerformance(startMonth, endMonth, codes);
            var oeeData = new List<dynamic>();
            foreach (var eqCode in codes)
            {
                var oeeItem = OeeResult.Find(x => x.EquipmentCode == eqCode);
                if (oeeItem == null)
                {
                    oeeData.Add(0);
                    continue;
                }
                oeeData.Add(oeeItem.OEE * 1000);
            }
            var result = new ChartsModel
            {
                XData = codes,
                YData = oeeData
            };
            return result;
        }

        /// <summary>
        /// 获取设备利用率、稼动率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel EquipmentOEE()
        {
            var OeeResult = _equipmentService.EquipmentOEEPerformance(startMonth, endMonth);
            var eqCodes = OeeResult.Select(t => t.EquipmentCode).ToList();
            var oeeData = new List<dynamic>();
            foreach (var eqCode in eqCodes)
            {
                var oeeItem = OeeResult.Find(x => x.EquipmentCode == eqCode);
                if (oeeItem == null)
                {
                    oeeData.Add(0);
                    continue;
                }
                oeeData.Add(oeeItem.OEE * 1000);
            }
            var result = new ChartsModel
            {
                XData = eqCodes,
                YData = oeeData
            };
            return result;
        }

        /// <summary>
        /// 获取设备利用率、稼动率[条形统计图或柱状图]
        /// </summary>
        /// <returns></returns>
        private ChartsModel EquipmentOEE(List<EquipmentStatusRecord> equipmentStatusRecords, List<string> codes)
        {
            var OeeResult = _equipmentService.EquipmentOEEPerformance(startMonth, endMonth, equipmentStatusRecords, codes);
            var oeeData = new List<dynamic>();//设备故障率
            foreach (var eqCode in codes)
            {
                var oeeItem = OeeResult.Find(x => x.EquipmentCode == eqCode);
                if (oeeItem == null)
                {
                    oeeData.Add(0);
                    continue;
                }
                oeeData.Add(oeeItem.OEE * 1000);
            }
            var result = new ChartsModel
            {
                XData = codes,
                YData = oeeData
            };
            return result;
        }

        /// <summary>
        /// 获取设备妥善率、设备故障率[饼状图]
        /// </summary>
        /// <returns></returns>
        private IEnumerable<PieChartModel> GetEquipmentMTBF_Pie()
        {
            var MTBFResult = _equipmentService.EquipmentMTBFPerformance(startMonth, endMonth);
            var eqCodes = MTBFResult.Select(t => t.EquipmentCode).ToList();
            foreach (var eqCode in eqCodes)
            {
                var mtbfItem = MTBFResult.Find(x => x.EquipmentCode == eqCode);
                if (mtbfItem == null)
                {
                    yield return new PieChartModel
                    {
                        Name = eqCode,
                        Value = 0
                    };
                    continue;
                }
                var mtbf = decimal.Round((decimal)mtbfItem.MTBF / 60 / 60, 2);
                yield return new PieChartModel
                {
                    Name = eqCode,
                    Value = mtbf
                };
            }
        }

        /// <summary>
        /// 获取设备组件距离下次保养的时间
        /// </summary>
        /// <param name="equipmentCode">设备编码</param>
        /// <param name="partCode">组件编码</param>
        /// <returns></returns>
        private static int GetDay(string equipmentCode, string partCode)
        {
            using EquipmentMaintainRecordRepository equipmentMaintain = new EquipmentMaintainRecordRepository();
            var maintainTime = equipmentMaintain.Where(x => x.EquipmentCode.Equals(equipmentCode) && x.PartCode.Equals(partCode)).OrderByDescending(x => x.Id).First().MaintainTime;
            return maintainTime != null ? (maintainTime - DateTime.Now).Value.Days : 0;
        }

        /// <summary>
        /// 保养规则时间间隔转换
        /// </summary>
        /// <param name="indicator">时间间隔数量</param>
        /// <param name="indicatorType">时间间隔类型</param>
        /// <param name="month">月</param>
        /// <returns></returns>
        private bool ExistsMonth(int? indicator, int? indicatorType, int month = 0)
        {
            var type = (IndicatorTypeEnums)indicatorType;
            if (indicator == null)
            {
                return false;
            }
            //其他方式
            else if (month == 0)
            {
                return type == IndicatorTypeEnums.Kilometre || type == IndicatorTypeEnums.Frequency || type == IndicatorTypeEnums.Number;
            }
            //一个月内
            else if (month == 1)
            {
                return type == IndicatorTypeEnums.Day && indicator <= 31
                      || (type == IndicatorTypeEnums.Month && indicator == 1);
            }
            //三个月内
            else if (month == 3)
            {
                return type == IndicatorTypeEnums.Day && indicator <= 30 * 3 && indicator > 31
                        || (type == IndicatorTypeEnums.Month && indicator <= 3 && indicator > 1)
                        || (type == IndicatorTypeEnums.Quarter && indicator == 1);
            }
            //6个月内
            else if (month == 6)
            {
                return type == IndicatorTypeEnums.Day && indicator <= 30 * 6 && indicator > 182
                      || (type == IndicatorTypeEnums.Month && indicator <= 6 && indicator > 3)
                      || (type == IndicatorTypeEnums.Quarter && indicator == 2);
            }
            //12个月内
            else if (month == 12)
            {
                return type == IndicatorTypeEnums.Day && indicator <= 365 && indicator > 182
                    || (type == IndicatorTypeEnums.Month && indicator <= 12 && indicator > 6)
                    || (type == IndicatorTypeEnums.Quarter && indicator == 4)
                    || type == IndicatorTypeEnums.Year && indicator == 1;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取累计故障明细（查询条件：停机时间年月日）
        /// </summary>
        /// <param name="beginDay"></param>
        /// <param name="endDay"></param>
        /// <returns></returns>
        public DataTable GetEquipmentBreakdownInfoByDay(string beginDay, string endDay, string eqType)
        {
            var sqlWhere = "";
            if (!string.IsNullOrEmpty(eqType))
            {
                sqlWhere = " and EquipmentCode in ('{0}')";
                sqlWhere = string.Format(sqlWhere, string.Join("','", eqType.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            string sql = $@"--累计故障明细 汇总分钟数
                            declare @beginday varchar(100),@endday varchar(100)
                            set @beginday = '{beginDay}'
                            set @endday = '{endDay}'
                            if OBJECT_id('tempdb..#temp')  is not NULL 
                            drop TABLE #temp
                            select * into #temp from (
	                            select  CONVERT(varchar, Created, 111) as Date,
		                            MAX(e.EquipmentName) as EquipmentName, 
		                            EquipmentCode,
		                            Alarm,
		                            MIN(e.Created) as startTime, 
		                            MAX(e.Updated) as endTime, 
		                            SUM(DATEDIFF(MINUTE, Created, Updated)) as summaryTime,
                                    '' as HandleInfo,'' as HandleUser,'' as Reason
                                 from	equipmentalarm e
                                 WHERE	e.Created >= @beginday and e.Created <= @endday {sqlWhere}
                                 group by  EquipmentCode, Alarm, CONVERT(varchar, Created, 111)  
                            )as a
		                    select * from #temp  where summaryTime>=1 order by EquipmentCode,startTime desc;
                            --查询所选时间段所有处理记录
                            select e.EquipmentCode, CONVERT(varchar,e.Created,111) as Created,
	                            e.Alarm ,eh.HandleInfo ,eh.HandleUser,eh.Reason
                            from equipmentalarm e
                            inner join equipmentalarmHandle eh on e.id = eh.EquipmentAlarmRecordId
                            where e.Created BETWEEN @beginday and @endday	 {sqlWhere}";

            var result = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            DataTable resDt = result.Tables[0];//返回数据
            DataTable handleDt = result.Tables[1];
            ////对查询结果进行分组求和处理，同一天相同设备汇总故障时间
            var res = from c in handleDt.AsEnumerable()
                      group c by new
                      {
                          Created = c.Field<string>("Created"),
                          EquipmentCode = c.Field<string>("EquipmentCode"),
                          Alarm = c.Field<string>("Alarm")
                      } into s
                      select new
                      {
                          Created = s.Select(c => c.Field<string>("Created")).First(),
                          EquipmentCode = s.Select(c => c.Field<string>("EquipmentCode")).First(),
                          Alarm = s.Select(c => c.Field<string>("Alarm")).First(),
                          //HandleInfo = string.Join(",", s.Select(c => c.Field<string>("HandleInfo")?.ToString().Trim())),
                          //HandleUser = string.Join(",", s.Select(c => c.Field<string>("HandleUser")?.ToString().Trim()))

                          HandleInfo = s.Select(c => c.Field<string>("HandleInfo")).First(),
                          HandleUser = s.Select(c => c.Field<string>("HandleUser")).First(),
                          Reason = s.Select(c => c.Field<string>("Reason")).First()
                      };
            DataTable handleDt_new = handleDt.Clone();
            res.ToList().ForEach(p => handleDt_new.Rows.Add(p.EquipmentCode, p.Created, p.Alarm, p.HandleInfo, p.HandleUser,p.Reason));
            var resDtEnum = resDt.AsEnumerable();
            foreach (DataRow dr in handleDt_new.Rows)
            {
                var s = resDtEnum.Where(i => i.Field<string>("EquipmentCode") == dr["EquipmentCode"].ToString() && i.Field<string>("Date") == dr["Created"].ToString() && i.Field<string>("Alarm") == dr["Alarm"].ToString()).FirstOrDefault();
                if (s != null)
                {
                    s["HandleInfo"] = dr["HandleInfo"];
                    s["HandleUser"] = dr["HandleUser"];
                    s["Reason"] = dr["Reason"];
                }
            }
            return resDt;
        }

        /// <summary>
        /// 月故障分析
        /// </summary>
        /// <param name="beginMonth"></param>
        /// <param name="endMonth"></param>
        /// <returns></returns>
        public DataTable GetEquipmentBreakdownOfMonthInfoByMonth(string beginMonth, string endMonth, string eqType)
        {
            var month = Convert.ToDateTime(beginMonth);
            var statr = month.AddDays(1 - month.Day);
            var end = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);
            var sqlWhere = "";
            if (!string.IsNullOrEmpty(eqType))
            {
                sqlWhere = " and EquipmentCode in ('{0}')";
                sqlWhere = string.Format(sqlWhere, string.Join("','", eqType.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }
            string sql = $@" select * from (
                                        select
	                                    e.id as id,--序号
	                                    e.Created ,--时间
	                                    EquipmentName,--机台
	                                    EquipmentCode,--设备编号
	                                    Alarm ,--故障原因
                                        e.Updated,

	                                    minutes=DATEDIFF(MINUTE, e.Created, e.Updated) ,--分钟数
	                                    eh.HandleInfo,--故障原因及维修方法
	                                    eh.HandleUser,  --维修人员
                                        eh.Reason 
                                    from equipmentalarm e
                                    left join equipmentalarmHandle eh on e.id = eh.EquipmentAlarmRecordId 
                                    where e.Created>='{statr}' and e.Created<='{end}'  {sqlWhere}
                            )t
                            where t.minutes>=1 order by  EquipmentCode,Created desc";
            var result = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            return result;
        }

        #region lsw
        /// <summary>
        /// 故障統計分析
        /// </summary>
        /// <returns></returns>
        public Tuple<dynamic, int> FaultStatistics(string begin, string end, string eqType)
        {
            var sqlWhere = "";
            if (!string.IsNullOrEmpty(eqType))
            {
                sqlWhere = " and t1.Code in ('{0}')";
                sqlWhere = string.Format(sqlWhere, string.Join("','", eqType.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }// t3.ruleName
            var tempSqlWhere = sqlWhere.Replace("Code", "equipmentcode");
            var sql = @$" 
                    if OBJECT_ID('tempdb..#equipmentTempAlarm') is not null drop table #equipmentTempAlarm;
                    with CTE as (
                        select ROW_NUMBER() over (partition by equipmentcode,convert(varchar(16),created,120),convert(varchar(16),updated,120) 
                        order by equipmentcode) as rownumber, *
	                    from equipmentalarm  t1
                        where 1=1  {tempSqlWhere} and  t1.Created>='{begin}' and t1.Created<='{end}'
                    )
                    select * into #equipmentTempAlarm from CTE 
                    delete from #equipmentTempAlarm where rownumber>1

                    select * from (select EquipmentName ,Alarm, AlarmStatr=t1.Created, t1.EquipmentCode,
                                                      AlarmEnd=t1.Updated,t2.HandleInfo, HandleUser=ISNULL(t2.HandleUser,''), HandleTime= t2.Created,
                                                      eqType=t3.name,sumTimeAlarm=(datediff(minute, t1.Created,t1.Updated)),FaultType=d.Name,
                                                      Reason=t2.Reason
                                             from #equipmentTempAlarm t1 
                                             left join equipmentalarmHandle t2 on t1.id=t2.EquipmentAlarmRecordId
                                             left join dictdetail d on d.dictid=44 and d.code=t1.faultType
                                             left join (
                                                  select t1.Code, t2.Name, ruleName=t3.Name ,eqTypeCode=t3.code from equipment t1
                                                  inner join equipmenttype t2 on t1.EquipmentTypeId=t2.Id
                                                  inner join equipmentmaintainrule t3 on t2.Code=t3.Code
                                                  where 1=1 {sqlWhere}
                                             ) t3 on t3.Code=t1.EquipmentCode ";
            if (!string.IsNullOrEmpty(eqType))
            {
                sql += "  and t1.EquipmentCode  in ('{0}')";
                var ss = eqType.Split(',', StringSplitOptions.RemoveEmptyEntries);
                sql = string.Format(sql, string.Join("','", ss));
            }
            sql += " )tt where sumTimeAlarm>=1 ";
            sql += "order by tt.EquipmentCode, tt.AlarmStatr DESC ";

            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            return new Tuple<dynamic, int>(dt, dt.Rows.Count);

        }

        /// <summary>
        /// 故障統計分析
        /// </summary>
        /// <returns></returns>
        public Tuple<dynamic, int> FaultStatisticsRepeat(string begin, string end, string eqType)
        {
            int strCount = 0;
            var table = DALHelper.GetFreeSql().Ado.ExecuteDataTable("select Value from dictdetail where code='repeatCount'");
            if (table.Rows.Count > 0)
            {
                int.TryParse(table.Rows[0][0].ToString(), out strCount);
            }

            var sqlWhere = "";
            if (!string.IsNullOrEmpty(eqType))
            {
                sqlWhere = " and t1.Code in ('{0}')";
                sqlWhere = string.Format(sqlWhere, string.Join("','", eqType.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }// t3.ruleName
            var tempSqlWhere = sqlWhere.Replace("Code", "equipmentcode");
            var sql = @$" 
                    if OBJECT_ID('tempdb..#equipmentTempAlarmRepeat') is not null drop table #equipmentTempAlarmRepeat;
                    with CTE as (
                        select ROW_NUMBER() over (partition by equipmentcode,convert(varchar(16),created,120),convert(varchar(16),updated,120) 
                        order by equipmentcode) as rownumber, *
	                    from equipmentalarm  t1
                        where 1=1  {tempSqlWhere} and  t1.Created>='{begin}' and t1.Created<='{end}'
                    )
                    select * into #equipmentTempAlarmRepeat from CTE 
                    delete from #equipmentTempAlarmRepeat where rownumber>1
                    select * from (select EquipmentName ,Alarm, AlarmStatr=t1.Created, t1.EquipmentCode,
                                                      AlarmEnd=t1.Updated,t2.HandleInfo, HandleUser=ISNULL(t2.HandleUser,''), HandleTime= t2.Created,
                                                      eqType=t3.name,sumTimeAlarm=(datediff(minute, t1.Created,t1.Updated))  ,FaultType=d.Name  ,
                                                      Reason=t2.Reason
                                                     
                                             from #equipmentTempAlarmRepeat t1 
                                             left join equipmentalarmHandle t2 on t1.id=t2.EquipmentAlarmRecordId
                                             left join dictdetail d on d.dictid=44 and d.code=t1.faultType
                                             left join (
                                                  select t1.Code, t2.Name, ruleName=t3.Name ,eqTypeCode=t3.code from equipment t1
                                                  inner join equipmenttype t2 on t1.EquipmentTypeId=t2.Id
                                                  inner join equipmentmaintainrule t3 on t2.Code=t3.Code
                                                   where 1=1  {sqlWhere}
                                             ) t3 on t3.Code=t1.EquipmentCode ";
            if (!string.IsNullOrEmpty(eqType))
            {
                sql += "  and t1.EquipmentCode in ('{0}')";
                var ss = eqType.Split(',', StringSplitOptions.RemoveEmptyEntries);
                sql = string.Format(sql, string.Join("','", ss));
            }
            sql += " )tt where sumTimeAlarm>=1 ";

            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            var res = from c in dt.AsEnumerable()
                      group c by new
                      {
                          EquipmentCode = c.Field<string>("EquipmentCode"),
                          EquipmentName = c.Field<string>("EquipmentName"),
                          Alarm = c.Field<string>("Alarm"),
                          eqType = c.Field<string>("eqType")
                      } into s
                      select new
                      {
                          EquipmentCode = s.Select(c => c.Field<string>("EquipmentCode")).First(),
                          EquipmentName = s.Select(c => c.Field<string>("EquipmentName")).First(),
                          Alarm = s.Select(c => c.Field<string>("Alarm")).First(),
                          eqType = s.Select(c => c.Field<string>("eqType")).First(),
                          AlarmStatr = s.Select(c => c.Field<DateTime>("AlarmStatr")).Min().ToString("yyyy-MM-dd HH:mm"),
                          AlarmEnd = s.Select(c => c.Field<DateTime>("AlarmEnd")).Max().ToString("yyyy-MM-dd HH:mm"),
                          sumTimeAlarm = s.Select(c => c.Field<int>("sumTimeAlarm")).Sum(),

                          //HandleUser = string.Join(",", s.Select(c => c.Field<string>("HandleUser")?.ToString()).Where(t => !string.IsNullOrWhiteSpace(t))),
                          //HandleTime = string.Join(",", s.Select(c => c.Field<DateTime?>("HandleTime")?.ToString()).Where(t => !string.IsNullOrWhiteSpace(t)))

                          FaultType = s.Select(c => c.Field<string>("FaultType")).First(),
                          Reason=s.Select(c=>c.Field<string>("Reason")).First(),

                          HandleInfo = s.Select(c => c.Field<string>("HandleInfo")).Where(t => !string.IsNullOrWhiteSpace(t)).FirstOrDefault(),
                          HandleUser = s.Select(c => c.Field<string>("HandleUser")).Where(t => !string.IsNullOrWhiteSpace(t)).FirstOrDefault(),
                          HandleTime = s.Select(c => c.Field<DateTime?>("HandleTime")).Where(t => t != null).FirstOrDefault(),
                          Count = s.Count()
                      };

            var item1Temp = res.Where(x => x.sumTimeAlarm > 0).OrderByDescending(x => x.Count).OrderByDescending(x => x.EquipmentCode).ToList();
            var item1 = from c in item1Temp
                        where c.Count > strCount
                        group c by new
                        {
                            EquipmentCode = c.EquipmentCode,
                            EquipmentName = c.EquipmentName,
                            eqType = c.eqType,
                            AlarmStatr = c.AlarmStatr,
                            AlarmEnd = c.AlarmEnd
                        } into s
                        select new
                        {
                            EquipmentCode = s.Select(c => c.EquipmentCode).First(),
                            EquipmentName = s.Select(c => c.EquipmentName).First(),
                            Alarm = s.Select(c => c.Alarm).First(),
                            eqType = s.Select(c => c.eqType).First(),
                            AlarmStatr = s.Select(c => c.AlarmStatr).First(),
                            AlarmEnd = s.Select(c => c.AlarmEnd).First(),
                            sumTimeAlarm = s.Select(c => c.sumTimeAlarm).First(),
                            FaultType = s.Select(c => c.FaultType).First(),
                            HandleInfo = s.Select(c => c.HandleInfo).First(),
                            HandleUser = s.Select(c => c.HandleUser).First(),
                            HandleTime = s.Select(c => c.HandleTime).First(),
                            Reason = s.Select(c => c.Reason).First(),
                            Count = s.Select(c => c.Count).First(),
                        };
            return new Tuple<dynamic, int>(item1, res.Count());

        }
        #endregion


        /// <summary>
        /// 年度故障分析（提供计算月平均故障维修时间以及月平均故障间隔时间的数据）
        /// </summary>
        /// <param name="year">年</param>
        /// <returns>月份、故障次数、正常运作时间、维修时间，分别返回两个数据表</returns>
        public DataSet GetWarehouseAvgByYear(string year)
        {
            string sql = $@"--年度故障分析
                            ----仓库月平均时间 
                            declare @selectyear varchar(10) 
                            SET @selectyear = '{year}'

							--处理同设备同时间故障
                           if OBJECT_ID('tempdb..#equipmentTemp') is not null drop table #equipmentTemp;
						   with CTE as (
                             select ROW_NUMBER() over (partition by equipmentcode,convert(varchar(16),created,120),convert(varchar(16),updated,120) 
                             order by equipmentcode) as rownumber,
                             * from equipmentalarm 
                             where DATENAME(YEAR, Created)= @selectyear
                           )
                           select * into #equipmentTemp from CTE 
                           delete from #equipmentTemp where rownumber>1

                            if Object_id('Tempdb..#listEquipmentalarm')   is not null   drop table #listEquipmentalarm;
                                select * into #listEquipmentalarm from (
                                      select * from (
		                                select Alarm,EquipmentCode,Created=CONVERT(varchar(16),t1.Created, 120),Updated=CONVERT(varchar(16),t1.Updated, 120)
	                                    from #equipmentTemp t1
			                            where  DATENAME(YEAR, Created)= @selectyear
			                            group by t1.Alarm,t1.EquipmentCode,   CONVERT(varchar(16),t1.Created, 120) , CONVERT(varchar(16),t1.Updated, 120)
		                              )t
		                              where datediff(minute, t.Created, t.Updated) >=1
                            )t  
							
                            --------------------1、年度设备平均故障维修时间MTTR---------------------------------
                            SELECT
	                            SUM(c) AS counts,--故障次数
	                            SUM(u) AS repairTime,--维修时间（单位：分钟）
                                CASE	 m
			                            WHEN '01' THEN  'Jan.'
			                            WHEN '02' THEN  'Feb.'
			                            WHEN '03' THEN  'Mar.'
			                            WHEN '04' THEN  'Apr.'
			                            WHEN '05' THEN  'May.'
			                            WHEN '06' THEN  'Jun.'
			                            WHEN '07' THEN  'Jul.'
			                            WHEN '08' THEN  'Aug.'
			                            WHEN '09' THEN  'Sep.'
			                            WHEN '10' THEN  'Oct.'
			                            WHEN '11' THEN  'Nov.'
			                            WHEN '12' THEN  'Dec.'
	                            ELSE ''
	                            END AS months --月份
                            from(
		                            SELECT COUNT(*) AS c,
				                            '' AS u,
	 		                              DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm e
		                            GROUP BY DATENAME(MONTH, Created)
			                            --故障次数
                            UNION ALL
		                            SELECT
			                            '' AS c,
			                            SUM(DATEDIFF(MINUTE, Created, Updated)) AS u,
			                            --按月分组算维修时间总和
			                            DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm eh
		                            GROUP BY DATENAME(MONTH, Created)
			                            --维修时间 
                                ) t
                            GROUP BY m

                            ------------------------------2、年度设备平均故障间隔时间MTBF--------------------------------------
                            SELECT
	                            SUM(c) AS counts,--故障次数
	                            SUM(r) AS runningTime,--正常运作时间（单位：分钟）
	                            CASE	m
		                               WHEN '01' THEN 'Jan.'
		                               WHEN '02' THEN 'Feb.'
		                               WHEN '03' THEN 'Mar.'
		                               WHEN '04' THEN 'Apr.'
		                               WHEN '05' THEN 'May.'
		                               WHEN '06' THEN 'Jun.'
		                               WHEN '07' THEN 'Jul.'
		                               WHEN '08' THEN 'Aug.'
		                               WHEN '09' THEN 'Sep.'
		                               WHEN '10' THEN 'Oct.'
		                               WHEN '11' THEN 'Nov.'
		                               WHEN '12' THEN 'Dec.'
		                            ELSE ''
	                            END AS months --月份
                            FROM
	                            (
		                            SELECT COUNT(*) AS c,
			                              --故障次数 
			                              '' AS r,
			                            DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm e
		                            GROUP BY DATENAME(MONTH, Created)
                            UNION ALL
		                            SELECT '' AS c,
			                            SUM(DATEDIFF(MINUTE, Created, Updated)) AS r,
			                            --正常运行时间（单位：分钟） 
			                            DATENAME(MONTH, e.created) AS m
		                            FROM equipmentstatusrecord e
		                            WHERE  DATENAME(YEAR, created)= @selectyear
		                            GROUP BY	DATENAME(MONTH, e.created)
                             )tt
                            GROUP BY tt.m;

                            select Value from dictdetail where code='MTTR_detail'
                            select Value from dictdetail where code='MTBF_detail' 
                            --warehouseCount仓库数用于算MTBF总运行时间
                            select DISTINCT  w.Code WarehouseCode from warehouse w";
            DataSet result = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            result.Tables[0].TableName = "MTTR";
            result.Tables[1].TableName = "MTBF";

            result.Tables[2].TableName = "MTTRDic";
            result.Tables[3].TableName = "MTBFDic";

            result.Tables[4].TableName = "WarehouseData";
            return result;
        }

        /// <summary>
        /// 故障总结报表查询
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public Dictionary<string, List<EquipmentSummaryFailuresModel>> GetWarehouseFauilreSummaryByYear(string year)
        {
            string sql = $@"----故障总结 （设备状态是运行的数据）
                          declare @selectyear varchar(10)
                          set @selectyear = '{year}'
                          --处理同设备同时间故障
                          if OBJECT_ID('tempdb..#equipmentTemp') is not null drop table #equipmentTemp;

                          with CTE as (
                            select ROW_NUMBER() over (partition by equipmentcode,substring(convert(varchar,created,120),1,16),substring(convert(varchar,updated,120),1,16) 
                            order by equipmentcode) as rownumber,
                            * from equipmentalarm 
                            where DATENAME(YEAR, Created)= @selectyear
                          )
                          select * into #equipmentTemp from CTE 
                          delete from #equipmentTemp where rownumber>1

                          --MTTR
                          select
                          	SUM(c) as counts,--次数
                          	SUM(u) as time,--维修时间_分钟
	                          w as warehouseCode,--仓库
	                          case
	                          	m  
                                when '01' then 'Jan.'
	                          	when '02' then 'Feb.'
	                          	when '03' then 'Mar.'
	                          	when '04' then 'Apr.'
		                        when '05' then 'May.'
	                          	when '06' then 'Jun.'
		                        when '07' then 'Jul.'
	                            when '08' then 'Aug.'
	                            when '09' then 'Sep.'
                                when '10' then 'Oct.'
	                            when '11' then 'Nov.'
		                        when '12' then 'Dec.'
		                        else ''
	                            END as months--月份
                          from
	                          (
	                          select
	                          	COUNT(*) as c,--故障次数
                                '' as u,
		                        e2.WarehouseCode as w,--仓库
		                        DATENAME(MONTH, e.Created) as m --月份  
	                          from
		                          #equipmentTemp e
	                          left join equipment e2 on
		                          e.EquipmentCode = e2.Code
	                          where
		                          DATENAME(YEAR, e.Created)= @selectyear
		                          and DATEDIFF(MINUTE, e.Created, e.Updated)>=1
	                          GROUP BY
		                          DATENAME(MONTH, e.Created),
		                          e2.WarehouseCode
                          union ALL
	                   select
		                          '' as c,
		                          SUM(DATEDIFF(MINUTE, e.Created, e.Updated)) as u,
		                          e2.WarehouseCode as w,
		                          DATENAME(MONTH, e.Created) as m
	                          from
		                           #equipmentTemp e 
	                          left join equipment e2 on
		                          e2.Code = e.EquipmentCode
	                          where
		                          DATENAME(YEAR, e.Created)= @selectyear
		                          and DATEDIFF(MINUTE, e.Created, e.Updated)>=1
	                          GROUP by
		                          DATENAME(MONTH, e.Created),
		                          e2.WarehouseCode
		                          --维修时间
                          ) t
                          GROUP by
	                          t.m,
	                          t.w

                          --MTBF
                          select
	                          SUM(c) as counts,--次数
	                          SUM(r) as time,--正常运作时间_分钟
	                          w as warehouseCode,--仓库
	                          case
		                          m  
                          when '01' then 'Jan.'
		                          when '02' then 'Feb.'
		                          when '03' then 'Mar.'
		                          when '04' then 'Apr.'
		                          when '05' then 'May.'
		                          when '06' then 'Jun.'
		                          when '07' then 'Jul.'
		                          when '08' then 'Aug.'
		                          when '09' then 'Sep.'
		                          when '10' then 'Oct.'
		                          when '11' then 'Nov.'
		                          when '12' then 'Dec.'
		                          else ''
	                          END as months--月份
                          from
	                          (
	                          select
		                          COUNT(*) as c,
		                          --故障次数
                                  '' as r,
		                          e2.WarehouseCode as w,
		                          --仓库
		                          DATENAME(MONTH, e.Created) as m
		                          --月份  
	                          from
		                          #equipmentTemp e
	                          left join equipment e2 on
		                          e.EquipmentCode = e2.Code
	                          where
		                          DATENAME(YEAR, e.Created)= @selectyear
		                          and DATEDIFF(MINUTE, e.Created, e.Updated)>=1
	                          GROUP BY
		                          DATENAME(MONTH, e.Created),
		                          e2.WarehouseCode
                          union ALL
	                          select
		                          '' as c,
		                          SUM(DATEDIFF(MINUTE, Created, Updated)) as r,
		                          --正常运行时间（单位：分钟）
		                          e.WarehouseCode as w,
		                          DATENAME(MONTH, e.created) as m
	                          from
	                          	equipmentstatusrecord e
	                          where
	                          	EquipmentStatus = 'Running'
		                          and DATENAME(YEAR, created)= @selectyear
		                          and DATEDIFF(MINUTE, e.Created, e.Updated)>=1
	                          group by
		                          DATENAME(MONTH, e.created),
		                          e.WarehouseCode 
                          )tt
                          group by
	                          tt.m,
	                          tt.w;";
            DataSet result = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            result.Tables[0].TableName = "MTTR";
            result.Tables[1].TableName = "MTBF";
            //List<string> list = new List<string>() { "Jan.","Feb.","Mar.","Apr.","May.","Jun.","Jul.","Aug.","Sep.","Oct.","Nov.","Dec." };
            Dictionary<string, List<EquipmentSummaryFailuresModel>> dicRes = new Dictionary<string, List<EquipmentSummaryFailuresModel>>();
            List<EquipmentSummaryFailuresModel> mttrlist = new List<EquipmentSummaryFailuresModel>();
            List<EquipmentSummaryFailuresModel> mtbflist = new List<EquipmentSummaryFailuresModel>();
            DataTable warehouseCodeDt = DALHelper.GetFreeSql().Ado.ExecuteDataTable("select DISTINCT WarehouseCode  From equipment e");
            for (var i = 0; i < warehouseCodeDt.Rows.Count; i++)
            {
                string warehouseCode = warehouseCodeDt.Rows[i]["WarehouseCode"].ToString().Trim();
                var m = new EquipmentSummaryFailuresModel();
                m.warehouseCode = warehouseCode;
                var dtMTTR = result.Tables[0].AsEnumerable();
                m.JanCounts = dtMTTR.Where(x => x.Field<string>("months") == "Jan." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.JanTime = dtMTTR.Where(x => x.Field<string>("months") == "Jan." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.FebCounts = dtMTTR.Where(x => x.Field<string>("months") == "Feb." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.FebTime = dtMTTR.Where(x => x.Field<string>("months") == "Feb." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.MarCounts = dtMTTR.Where(x => x.Field<string>("months") == "Mar." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.MarTime = dtMTTR.Where(x => x.Field<string>("months") == "Mar." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.AprCounts = dtMTTR.Where(x => x.Field<string>("months") == "Apr." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.AprTime = dtMTTR.Where(x => x.Field<string>("months") == "Apr." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.MayCounts = dtMTTR.Where(x => x.Field<string>("months") == "May." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.MayTime = dtMTTR.Where(x => x.Field<string>("months") == "May." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.JunCounts = dtMTTR.Where(x => x.Field<string>("months") == "Jun." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.JunTime = dtMTTR.Where(x => x.Field<string>("months") == "Jun." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.JulCounts = dtMTTR.Where(x => x.Field<string>("months") == "Jul." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.JulTime = dtMTTR.Where(x => x.Field<string>("months") == "Jul." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.AugCounts = dtMTTR.Where(x => x.Field<string>("months") == "Aug." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.AugTime = dtMTTR.Where(x => x.Field<string>("months") == "Aug." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.SepCounts = dtMTTR.Where(x => x.Field<string>("months") == "Sep." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.SepTime = dtMTTR.Where(x => x.Field<string>("months") == "Sep." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.OctCounts = dtMTTR.Where(x => x.Field<string>("months") == "Oct." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.OctTime = dtMTTR.Where(x => x.Field<string>("months") == "Oct." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.NovCounts = dtMTTR.Where(x => x.Field<string>("months") == "Nov." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.NovTime = dtMTTR.Where(x => x.Field<string>("months") == "Nov." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                m.DecCounts = dtMTTR.Where(x => x.Field<string>("months") == "Dec." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                m.DecTime = dtMTTR.Where(x => x.Field<string>("months") == "Dec." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mttrlist.Add(m);

                var mm = new EquipmentSummaryFailuresModel();
                mm.warehouseCode = warehouseCode;
                var dtMTbf = result.Tables[1].AsEnumerable();
                mm.JanCounts = dtMTbf.Where(x => x.Field<string>("months") == "Jan." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.JanTime = dtMTbf.Where(x => x.Field<string>("months") == "Jan." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.FebCounts = dtMTbf.Where(x => x.Field<string>("months") == "Feb." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.FebTime = dtMTbf.Where(x => x.Field<string>("months") == "Feb." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.MarCounts = dtMTbf.Where(x => x.Field<string>("months") == "Mar." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.MarTime = dtMTbf.Where(x => x.Field<string>("months") == "Mar." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.AprCounts = dtMTbf.Where(x => x.Field<string>("months") == "Apr." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.AprTime = dtMTbf.Where(x => x.Field<string>("months") == "Apr." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.MayCounts = dtMTbf.Where(x => x.Field<string>("months") == "May." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.MayTime = dtMTbf.Where(x => x.Field<string>("months") == "May." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.JunCounts = dtMTbf.Where(x => x.Field<string>("months") == "Jun." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.JunTime = dtMTbf.Where(x => x.Field<string>("months") == "Jun." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.JulCounts = dtMTbf.Where(x => x.Field<string>("months") == "Jul." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.JulTime = dtMTbf.Where(x => x.Field<string>("months") == "Jul." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.AugCounts = dtMTbf.Where(x => x.Field<string>("months") == "Aug." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.AugTime = dtMTbf.Where(x => x.Field<string>("months") == "Aug." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.SepCounts = dtMTbf.Where(x => x.Field<string>("months") == "Sep." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.SepTime = dtMTbf.Where(x => x.Field<string>("months") == "Sep." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.OctCounts = dtMTbf.Where(x => x.Field<string>("months") == "Oct." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.OctTime = dtMTbf.Where(x => x.Field<string>("months") == "Oct." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.NovCounts = dtMTbf.Where(x => x.Field<string>("months") == "Nov." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.NovTime = dtMTbf.Where(x => x.Field<string>("months") == "Nov." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mm.DecCounts = dtMTbf.Where(x => x.Field<string>("months") == "Dec." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("counts")).FirstOrDefault();
                mm.DecTime = dtMTbf.Where(x => x.Field<string>("months") == "Dec." && x.Field<string>("warehouseCode") == warehouseCode).Select(x => x.Field<int>("time")).FirstOrDefault();
                mtbflist.Add(mm);

            }

            dicRes.Add("MTTR", mttrlist);
            dicRes.Add("MTBF", mtbflist);
            return dicRes;
        }

        /// <summary>
        /// 设备曲线 运行电流、振动
        /// </summary>
        public DataTable GetEqRunInfo(string eqCode, string begin, string end)
        {
            var sqlWhere = $" and  t1.Created>='{begin}' and t1.Created<='{end}' ";
            if (!string.IsNullOrEmpty(eqCode)) sqlWhere += $" and  t1.Code='{eqCode}'";
            var sql = @$"select * from (
	                            select
		                               rowIndex= ROW_NUMBER() over(partition by t1.code ,t1.Name , substring( convert(varchar,t1.Created,120),1,16)  order by t1.Created),
		                               t1.code ,t1.Name,t1.Created,
		                               t1.WalkVibrationVelocity,t1.WalkTemperature,
		                               t1.LiftVibrationVelocity,t1.LiftTemperature,
		                               t1.runCurrent,t1.liftCurrent
	                            from  equipmentdatarecordHistory t1 with(nolock)
	                            left join equipment  t2 on t1.Code=t2.Code
	                            left join  equipmenttype t3 on t3.Id=t2.EquipmentTypeId
	                            where t1.Status='运行'  and t3.Code='Stacker' {sqlWhere}
                            )t
                            where t.rowIndex=1 order by t.Name, t.Created";
            return DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
        }

        /// <summary>
        /// 获取设备类型 
        /// </summary>
        public DataTable GetEquipmentByCode()
        {
            var sql = @$"select t1.code, t1.name  from equipment  t1
                        left join  equipmenttype t2 on t1.EquipmentTypeId=t2.Id
                        where t2.Code='Stacker' and datediff(minute, t1.created, t1.updated)>=1 ";
            return DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
        }

        /// <summary>
        /// 故障报告 
        /// </summary>
        public Tuple<DataSet, int, string, string> GetFaultReport(string yyyyMonth, string eqCode)
        {
            var month = Convert.ToDateTime(yyyyMonth);
            //月初、月末
            var monthStart = month.AddDays(1 - month.Day);
            var monthEnd = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);

            //周一、周末
            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 = $" where  t1.Created>='{weekOne}' and t1.Created<='{weekend}' ";
            var sqlWhereMonth = $" where  t1.Created>='{monthStart}' and t1.Created<='{monthEnd}' ";
            var sqlWhereWeekTemp = $" where  t1.Created>='{weekOne}' and t1.Created<='{weekend}' ";

            List<AreaClass> areaList = new List<AreaClass>();
            string areaA = "";
            string areaL = "";
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhereWeek += " and t1.EquipmentCode in ('{0}') ";
                sqlWhereWeekTemp += " and t1.EquipmentCode in ('{0}') ";
                sqlWhereMonth += " and t1.EquipmentCode in ('{0}') ";

                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;

                    if (areaList[index].area == "A")
                    {
                        if (!string.IsNullOrEmpty(areaA))
                        {
                            areaA += ",";
                        }
                        areaA += areaList[index].value;
                    }

                    if (areaList[index].area == "L")
                    {
                        if (!string.IsNullOrEmpty(areaL))
                        {
                            areaL += ",";
                        }
                        areaL += areaList[index].value;
                    }
                }

                sqlWhereWeek = string.Format(sqlWhereWeek, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
                sqlWhereWeekTemp = string.Format(sqlWhereWeekTemp, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
                sqlWhereMonth = string.Format(sqlWhereMonth, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sql = GetSql(sqlWhereWeek, sqlWhereWeekTemp, sqlWhereMonth, areaList, areaA, areaL);
            var ds = DALHelper.GetFreeSql().Ado.ExecuteDataSet(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");
            return new Tuple<DataSet, int, string, string>(ds, diffDay, monthStr, weekStr);
        }

        public string GetSql(string sqlWhereWeek, string sqlWhereWeekTemp, string sqlWhereMonth, List<AreaClass> areaList, string areaA, string areaL)
        {
            var sqlMonth = "";
            var sqlWeek = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(sqlMonth))
                {
                    sqlMonth += " union all ";
                    sqlWeek += " union all ";
                }

                sqlMonth += @" select name = '{0}', warehousecode,
                                     sumTime = sum(datediff(minute, created, updated))
                            from  #listMonth t 
                            where  t.EquipmentCode in('{1}') 
	                        group by WarehouseCode ";
                sqlMonth = string.Format(sqlMonth, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));

                sqlWeek += @"select name = '{0}', warehousecode,
                                    sumTime = sum(datediff(minute, created, updated))
                            from  #listWeek t 
                            where  t.EquipmentCode in('{1}') 
	                        group by WarehouseCode";
                sqlWeek = string.Format(sqlWeek, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sqlWarehouse = "";
            if (!string.IsNullOrEmpty(areaA))
            {
                sqlWarehouse += @"select *, warehouseName = 'AGV',
                                category = '故障時間(分)',
								morning = '早班',
								morningSumtime = (
                                    select  sumtime = sum(datediff(minute, created, updated))
                                    from  #listWeek t  
								    where convert(varchar(100), created, 108) >= '08:00:00'and convert(varchar(100),created, 108)<= '19:59:59'
                                    and EquipmentCode in ('{0}')
								)
                                from(
                                    select  evening = '晚班',
                                    eveningSumtime = sum(datediff(minute, created, updated))
                                    from #listWeek
									where ((convert(varchar(100), created, 108) >= '20:00:00' and convert(varchar(100), created, 108) <= '23:59:59')
                                    or(convert(varchar(100), created, 108) >= '00:00:00'and convert(varchar(100), created, 108) <= '07:59:59'))
                                    and EquipmentCode in ('{1}')
								)t";
                string code = string.Join("','", areaA.Split(',', StringSplitOptions.RemoveEmptyEntries));
                sqlWarehouse = string.Format(sqlWarehouse, code, code);
            }
            if (!string.IsNullOrEmpty(areaL))
            {
                if (!string.IsNullOrEmpty(areaA))
                {
                    sqlWarehouse += " union all ";
                }
                sqlWarehouse += @"select *, warehouseName = '仓库',
                                category = '故障時間(分)',
								morning = '早班',
								morningSumtime = (
                                    select  sumtime = sum(datediff(minute, created, updated))
                                    from  #listWeek t  
								    where convert(varchar(100), created, 108) >= '08:00:00'and convert(varchar(100),created, 108)<= '19:59:59'
                                    and EquipmentCode in ('{0}')
								)
                                from(
                                    select  evening = '晚班',
                                    eveningSumtime = sum(datediff(minute, created, updated))
                                    from #listWeek
									where ((convert(varchar(100), created, 108) >= '20:00:00' and convert(varchar(100), created, 108) <= '23:59:59')
                                    or(convert(varchar(100), created, 108) >= '00:00:00'and convert(varchar(100), created, 108) <= '07:59:59'))
                                    and EquipmentCode in ('{1}')
								)t";
                string code = string.Join("','", areaL.Split(',', StringSplitOptions.RemoveEmptyEntries));
                sqlWarehouse = string.Format(sqlWarehouse, code, code);
            }

            var sql = @$"  -----------------本月公共数据源--------------------------------------------
                          if Object_id('Tempdb..#listMonth')   is not null   drop table #listMonth;
                          select * into #listMonth from (
                                  select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated ,t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId 
                                {sqlWhereMonth}
                          )t;
                         ----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
                          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 #listMonth e 
                                 )
                          delete from CTE WHERE  rownumber>1;

                         -----------------本周公共数据源--------------------------------------------
                        if Object_id('Tempdb..#listWeek')   is not null   drop table #listWeek;
                          select * into #listWeek from (
                             select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated ,t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
                              ,DATEDIFF(MINUTE,t1.created,t1.updated) as sumtime,t1.id as EquipmentAlarmId
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId 
                                  {sqlWhereWeek} and  datediff(minute, t1.created, t1.updated)>=1
                        )t;
                         ----------------listWeek 处理相同设备在同年月日时分数据>----------------
                          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 #listWeek e 
                                 )
                          delete from CTE WHERE  rownumber>1;  

                         ------------------本周设备总运行时间数据源----------------------------------------------
						 if Object_id('Tempdb..#listEquipmentstatusrecordWeek')   is not null   drop table #listEquipmentstatusrecordWeek;
                          select * into #listEquipmentstatusrecordWeek from (
                               select WarehouseCode  ,Created,t1.Updated
							   from  equipmentstatusrecord  t1
                               {sqlWhereWeekTemp}  and  datediff(minute, t1.created, t1.updated)>=1
                        )t

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

                        ----------------故障時間本周开始listWeek1 表格1-------------------------------
                        {sqlWeek}
                         ----------------故障時間本周结束-------------------------------

                         ----------------仓库故障本周周末早班、晚班开始 listmorningAndEvening2 表格2-------------------------------
                        {sqlWarehouse}
                        ----------------仓库故障本周早班、晚班结束-------------------------------

						----------------仓库本周设备运行早晚班 运行时间开始eqRun3 表格2------------
						select *, morning='早班',
								  morningSumtime=(
									  select   sumtime= sum(datediff(minute, created, updated))   
									  from  #listEquipmentstatusrecordWeek t  
									  where convert(varchar(100),created, 108)>='08:00:00'and convert(varchar(100),created, 108)<'19:59:59'
								 )
						  from (
									 select  evening='晚班' ,
										     eveningSumtime= sum(datediff(minute, created, updated))   
									 from #listEquipmentstatusrecordWeek
									 where ( convert(varchar(100),created, 108)>='20:00:00' and convert(varchar(100),created, 108) <='23:59:59')  
									 or( convert(varchar(100),created, 108)>='00:00:00'and convert(varchar(100),created, 108)<='07:59:59') 
								)t 
                         -------------仓库本周设备运行早晚班 运行时间结束------------

                        --------------本周故障時間列表超過40分开始listAlarmSum40 4-------------------------------------
		               select t.*,eh.HandleInfo,eh.HandleUser,eh.Reason 
		               From #listweek t left join equipmentalarmHandle eh  
		               on eh.EquipmentAlarmRecordId =t.EquipmentAlarmId
		               where t.sumtime>40
		               order by t.sumtime  DESC 

                        -------------本周故障時間列表超過40分结束-------------------------------------


                        ------------本周 仓库故障時間超過40分曲线图开始 quxianEcharts5-------------------------------------
						select t.* ,t1.name from (
								  select  warehousecode, 
										  sumtime= sum(datediff(minute, created, updated))  
								  from #listWeek  
								  group by warehousecode 
								  having   sum(datediff(minute, created, updated))  >=40
						)t
						left join warehouse t1 on t.warehousecode=t1.Code

                        ----仓库总运行时间 quxianEcharts6
						select WarehouseCode  ,
						sumTime =sum(datediff(minute, created, updated))   from #listEquipmentstatusrecordWeek
						group by WarehouseCode
                        -----------本周 仓库故障時間超過40分曲线图开始------------------------------------- 
                      
                       ---weekDefault 7 
                       select Value from dictdetail where code='weekDefault'

                       --妥善率目标 8
                       select Value from dictdetail where code='properlyTarget'
                       
                       --仓库可设定 9
                       select Value from dictdetail where code='factoryTarget'  ";
            return sql;
        }

        /// <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>
        public DataTable GetProperRate(string startTime, string endTime, string eqCode, string yearOrdaySelectVal, string timeFlag)
        {
            if (timeFlag == "year")
            {
                startTime = yearOrdaySelectVal + "-01-01";
                endTime = yearOrdaySelectVal + "-12-31";
            }
            else if (timeFlag == "day")
            {
                startTime = yearOrdaySelectVal;
                endTime = yearOrdaySelectVal;
            }

            var startDate = startTime + " 00:00:00";
            var endDate = endTime + " 23:59:59";
            var sqlWhere = $" where  t1.Created>='{startDate}' and t1.Created<='{endDate}' ";

            List<AreaClass> areaList = new List<AreaClass>();
            string areaA = "";
            string areaL = "";
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhere += " and t1.EquipmentCode in ('{0}') ";

                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;

                    if (areaList[index].area == "A")
                    {
                        if (!string.IsNullOrEmpty(areaA))
                        {
                            areaA += ",";
                        }
                        areaA += areaList[index].value;
                    }

                    if (areaList[index].area == "L")
                    {
                        if (!string.IsNullOrEmpty(areaL))
                        {
                            areaL += ",";
                        }
                        areaL += areaList[index].value;
                    }
                }

                sqlWhere = string.Format(sqlWhere, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sql = GetRateSql(sqlWhere, areaList, timeFlag);
            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            return dt;
        }

        public string GetRateSql(string sqlWhere, List<AreaClass> areaList, string timeFlag)
        {
            var sql = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(sql))
                {
                    sql += " union all ";
                }
                if (timeFlag == "year")
                {
                    sql += @"select name = '{0}', warehousecode,CONVERT(varchar(7),created,120) as date,
                                sumTime = sum(DATEDIFF(minute, created, updated))
                            from  #tempdbRate t 
                            where  t.EquipmentCode in('{1}') 
	                        group by WarehouseCode,CONVERT(varchar(7),created,120) ";
                }
                else
                {
                    sql += @"select name = '{0}', warehousecode,CONVERT(varchar,created,23) as date,
                                sumTime = sum(DATEDIFF(minute, created, updated))
                            from  #tempdbRate t 
                            where  t.EquipmentCode in('{1}') 
	                        group by WarehouseCode,CONVERT(varchar,created,23) ";
                }
                sql = string.Format(sql, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var resSql = @$"     
                         -----------------数据源--------------------------------------------
                          if Object_id('Tempdb..#tempdbRate') is not null drop table #tempdbRate;
                          select * into #tempdbRate from (
                             select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated ,t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId
                                  {sqlWhere} and DATEDIFF(minute, t1.created, t1.updated)>=1
                        )t;
                           ---处理相同设备同年月日时分故障--
                               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
                         -----------------时间段故障总结--------------------------------------------
                        {sql}
                      ";
            return resSql;
        }

        //AGV异常记录跟踪 统计故障发生次数
		public DataTable GetAGVAbnormalRecord(string startTime, string endTime, string eqCode,string alarm)
		{
			var startDate = startTime + " 00:00:01";
			var endDate = endTime + " 23:59:59";
			var sqlWhere = $" where  t1.Created>='{startDate}' and t1.Created<='{endDate}'";
            if (!string.IsNullOrEmpty(alarm))
            {
                sqlWhere += $"  and t1.alarm = '{alarm}' ";
			}

			List<AreaClass> areaList = new List<AreaClass>();
			string areaA = "";
			string areaL = "";
			if (!string.IsNullOrEmpty(eqCode))
			{
				sqlWhere += " and t1.EquipmentCode in ('{0}') ";

				string code = "";
				areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

				for (var index = 0; index < areaList.Count; index++)
				{
					if (!string.IsNullOrEmpty(code))
					{
						code += ",";
					}
					code += areaList[index].value;

					if (areaList[index].area == "A")
					{
						if (!string.IsNullOrEmpty(areaA))
						{
							areaA += ",";
						}
						areaA += areaList[index].value;
					}

					if (areaList[index].area == "L")
					{
						if (!string.IsNullOrEmpty(areaL))
						{
							areaL += ",";
						}
						areaL += areaList[index].value;
					}
				}

				sqlWhere = string.Format(sqlWhere, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
			}

			var sql = GetAGVAbnormalRecordSql(sqlWhere, areaList,alarm);
			var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
			return dt;
		}

		public string GetAGVAbnormalRecordSql(string sqlWhere, List<AreaClass> areaList,string alarm)
		{
			var sql = "";
			for (var index = 0; index < areaList.Count; index++)
			{
				if (!string.IsNullOrEmpty(sql))
				{
					sql += " union all ";
				}
                if (!string.IsNullOrEmpty(alarm))
                {
					sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            CONVERT(varchar,created,23) as date,
	                            EquipmentCode,
	                            COUNT(*) as count,
                                Alarm
                            --	sumTime = sum(DATEDIFF(minute, created, updated))
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
	                            Alarm,
	                            CONVERT(varchar,created,23) ";
				}
                else
                {
					sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            CONVERT(varchar,created,23) as date,
	                            EquipmentCode,
	                            COUNT(*) as count
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
	                            CONVERT(varchar,created,23) ";
				}
				
				sql = string.Format(sql, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
			}

			var resSql = @$"     
                         -----------------数据源--------------------------------------------
                          if Object_id('Tempdb..#tempdbRate') is not null drop table #tempdbRate;
                          select * into #tempdbRate from (
                                 select 
	                                    t1.Warehousecode,
	                                    t1.EquipmentCode,
	                                    t1.EquipmentName,
	                                    t1.EquipmentPropName,
	                                    t1.Alarm,
	                                    t1.Created,
	                                    t1.Updated,
	                                    t2.EquipmentTypeId,
	                                    t3.Code as EquipmentTypeCode,
	                                    t3.[Name] as EquipmentTypeName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId
                                  {sqlWhere} and DATEDIFF(minute, t1.created, t1.updated)>=1
                        )t;
                     -------------------处理相同设备同年月日时分故障------------------------------
                      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
                     --------------------时间段故障总结--------------------------
                        {sql}
                      ";
			return resSql;
		}

		/// <summary>
		/// 仓库月平均时间 
		/// </summary>
		/// <param name="year"></param>
		/// <returns></returns>
		/// <exception cref="NotImplementedException"></exception>
		public DataSet GetWarehouseAvgByYearForAGV(string year)
        {
            string sql = $@"--年度故障分析
                            ----仓库月平均时间 
                            declare @selectyear varchar(10) 
                            SET @selectyear = '{year}'

							--处理同设备同时间故障
                           if OBJECT_ID('tempdb..#equipmentTemp') is not null drop table #equipmentTemp;
						   with CTE as (
                             select ROW_NUMBER() over (partition by equipmentcode,convert(varchar(16),created,120),convert(varchar(16),updated,120) 
                             order by equipmentcode) as rownumber,
                             * from equipmentalarm 
                             where DATENAME(YEAR, Created)= @selectyear and EquipmentName like '%AGV%'
                           )
                           select * into #equipmentTemp from CTE 
                           delete from #equipmentTemp where rownumber>1

                            if Object_id('Tempdb..#listEquipmentalarm')   is not null   drop table #listEquipmentalarm;
                                select * into #listEquipmentalarm from (
                                      select * from (
		                                select Alarm,EquipmentCode,Created=CONVERT(varchar(16),t1.Created, 120),Updated=CONVERT(varchar(16),t1.Updated, 120)
	                                    from #equipmentTemp t1
			                            where  DATENAME(YEAR, Created)= @selectyear
			                            group by t1.Alarm,t1.EquipmentCode,   CONVERT(varchar(16),t1.Created, 120) , CONVERT(varchar(16),t1.Updated, 120)
		                              )t
		                              where datediff(minute, t.Created, t.Updated) >=1
                            )t  
							
                            --------------------1、年度设备平均故障维修时间MTTR---------------------------------
                            SELECT
	                            SUM(c) AS counts,--故障次数
	                            SUM(u) AS repairTime,--维修时间（单位：分钟）
                                CASE	 m
			                            WHEN '01' THEN  'Jan.'
			                            WHEN '02' THEN  'Feb.'
			                            WHEN '03' THEN  'Mar.'
			                            WHEN '04' THEN  'Apr.'
			                            WHEN '05' THEN  'May.'
			                            WHEN '06' THEN  'Jun.'
			                            WHEN '07' THEN  'Jul.'
			                            WHEN '08' THEN  'Aug.'
			                            WHEN '09' THEN  'Sep.'
			                            WHEN '10' THEN  'Oct.'
			                            WHEN '11' THEN  'Nov.'
			                            WHEN '12' THEN  'Dec.'
	                            ELSE ''
	                            END AS months --月份
                            from(
		                            SELECT COUNT(*) AS c,
				                            '' AS u,
	 		                              DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm e
		                            GROUP BY DATENAME(MONTH, Created)
			                            --故障次数
                            UNION ALL
		                            SELECT
			                            '' AS c,
			                            SUM(DATEDIFF(MINUTE, Created, Updated)) AS u,
			                            --按月分组算维修时间总和
			                            DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm eh
		                            GROUP BY DATENAME(MONTH, Created)
			                            --维修时间 
                                ) t
                            GROUP BY m

                            ------------------------------2、年度设备平均故障间隔时间MTBF--------------------------------------
                            SELECT
	                            SUM(c) AS counts,--故障次数
	                            SUM(r) AS runningTime,--正常运作时间（单位：分钟）
	                            CASE	m
		                               WHEN '01' THEN 'Jan.'
		                               WHEN '02' THEN 'Feb.'
		                               WHEN '03' THEN 'Mar.'
		                               WHEN '04' THEN 'Apr.'
		                               WHEN '05' THEN 'May.'
		                               WHEN '06' THEN 'Jun.'
		                               WHEN '07' THEN 'Jul.'
		                               WHEN '08' THEN 'Aug.'
		                               WHEN '09' THEN 'Sep.'
		                               WHEN '10' THEN 'Oct.'
		                               WHEN '11' THEN 'Nov.'
		                               WHEN '12' THEN 'Dec.'
		                            ELSE ''
	                            END AS months --月份
                            FROM
	                            (
		                            SELECT COUNT(*) AS c,
			                              --故障次数 
			                              '' AS r,
			                            DATENAME(MONTH, Created) AS m
		                            FROM #listEquipmentalarm e
		                            GROUP BY DATENAME(MONTH, Created)
                            UNION ALL
		                            SELECT '' AS c,
			                            SUM(DATEDIFF(MINUTE, Created, Updated)) AS r,
			                            --正常运行时间（单位：分钟） 
			                            DATENAME(MONTH, e.created) AS m
		                            FROM equipmentstatusrecord e
		                            WHERE  DATENAME(YEAR, created)= @selectyear and EquipmentName like '%AGV%'
		                            GROUP BY	DATENAME(MONTH, e.created)
                             )tt
                            GROUP BY tt.m;

                            select Value from dictdetail where code='MTTR_detail'
                            select Value from dictdetail where code='MTBF_detail' 
                            --warehouseCount 仓库数用于算MTBF总运行时间
                            select DISTINCT  WarehouseCode   from equipment e  where Name  like '%AGV%'";
            DataSet result = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            result.Tables[0].TableName = "MTTR";
            result.Tables[1].TableName = "MTBF";

            result.Tables[2].TableName = "MTTRDic";
            result.Tables[3].TableName = "MTBFDic";

            result.Tables[4].TableName = "WarehouseData";
            return result;
        }


        /// <summary>
        /// 故障次数
        /// </summary>
        /// <param name="yyyyMonth"></param>
        /// <param name="eqCode"></param>
        /// <returns></returns>
        public List<Tuple<dynamic, int>> GetFaultCount(string yyyyMonth, string eqCode)
        {
            var month = Convert.ToDateTime(yyyyMonth);

            //月初、月末
            var monthStart = month.AddDays(1 - month.Day);
            var monthEnd = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);

            //周一、周末
            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");

            string eqType = "";
            List<AreaClass> areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(eqType))
                {
                    eqType += ",";
                }
                eqType += areaList[index].value;
            }
            var result = FaultStatisticsRepeatNew(weekOne, weekEnd, eqType);
            var resultMonth = FaultStatisticsRepeatNew(monthStart.ToString("yyyy-MM-dd 00:00:00"), monthEnd.ToString("yyyy-MM-dd 23:59:59"), eqType);
            List<Tuple<dynamic, int>> res = new List<Tuple<dynamic, int>>() { result, resultMonth };
            return res;

        }

        /// <summary>
        /// 故障分析（重复次数） 时间段
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="eqCode"></param>
        /// <returns></returns>
        public Tuple<dynamic, int> GetFaultCount(string startTime,string endTime, string eqCode)
        {
           
            string eqType = "";
            List<AreaClass> areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(eqType))
                {
                    eqType += ",";
                }
                eqType += areaList[index].value;
            }
            var result = FaultStatisticsRepeatNew(startTime, endTime, eqType);
            return result;

        }

        public Tuple<dynamic, int> FaultStatisticsRepeatNew(string begin, string end, string eqType)
        {
            int strCount = 0;
            var table = DALHelper.GetFreeSql().Ado.ExecuteDataTable("select Value from dictdetail where code='repeatCount'");
            if (table.Rows.Count > 0)
            {
                int.TryParse(table.Rows[0][0].ToString(), out strCount);
            }

            var sqlWhere = "";
            if (!string.IsNullOrEmpty(eqType))
            {
                sqlWhere = " and t1.Code in ('{0}')";
                sqlWhere = string.Format(sqlWhere, string.Join("','", eqType.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }// t3.ruleName
            var tempSqlWhere = sqlWhere.Replace("Code", "equipmentcode");
            var sql = @$" 
                    if OBJECT_ID('tempdb..#equipmentTempAlarmRepeat') is not null drop table #equipmentTempAlarmRepeat;
                    with CTE as (
                        select ROW_NUMBER() over (partition by equipmentcode,convert(varchar(16),created,120),convert(varchar(16),updated,120) 
                        order by equipmentcode) as rownumber, *
	                    from equipmentalarm  t1
                        where 1=1  {tempSqlWhere} and  t1.Created>='{begin}' and t1.Created<='{end}'
                    )
                    select * into #equipmentTempAlarmRepeat from CTE 
                    delete from #equipmentTempAlarmRepeat where rownumber>1
                    select * from (select EquipmentName ,Alarm, AlarmStatr=t1.Created, t1.EquipmentCode,
                                                      AlarmEnd=t1.Updated,t2.HandleInfo, HandleUser=ISNULL(t2.HandleUser,''), HandleTime= t2.Created,
                                                      eqType=t3.name,sumTimeAlarm=(datediff(minute, t1.Created,t1.Updated))  ,FaultType=d.Name  
                                                     
                                             from #equipmentTempAlarmRepeat t1 
                                             left join equipmentalarmHandle t2 on t1.id=t2.EquipmentAlarmRecordId
                                             left join dictdetail d on d.dictid=44 and d.code=t1.faultType
                                             left join (
                                                  select t1.Code, t2.Name, ruleName=t3.Name ,eqTypeCode=t3.code from equipment t1
                                                  inner join equipmenttype t2 on t1.EquipmentTypeId=t2.Id
                                                  inner join equipmentmaintainrule t3 on t2.Code=t3.Code
                                                   where 1=1  {sqlWhere}
                                             ) t3 on t3.Code=t1.EquipmentCode ";
            if (!string.IsNullOrEmpty(eqType))
            {
                sql += "  and t1.EquipmentCode in ('{0}')";
                var ss = eqType.Split(',', StringSplitOptions.RemoveEmptyEntries);
                sql = string.Format(sql, string.Join("','", ss));
            }
            sql += " )tt where sumTimeAlarm>=1 ";

            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            var res = from c in dt.AsEnumerable()
                      group c by new
                      {
                          EquipmentCode = c.Field<string>("EquipmentCode"),
                          EquipmentName = c.Field<string>("EquipmentName"),
                          Alarm = c.Field<string>("Alarm"),
                          eqType = c.Field<string>("eqType")
                      } into s
                      select new
                      {
                          EquipmentCode = s.Select(c => c.Field<string>("EquipmentCode")).First(),
                          EquipmentName = s.Select(c => c.Field<string>("EquipmentName")).First(),
                          Alarm = s.Select(c => c.Field<string>("Alarm")).First(),
                          eqType = s.Select(c => c.Field<string>("eqType")).First(),
                          AlarmStatr = s.Select(c => c.Field<DateTime>("AlarmStatr")).Min().ToString("yyyy-MM-dd HH:mm"),
                          AlarmEnd = s.Select(c => c.Field<DateTime>("AlarmEnd")).Max().ToString("yyyy-MM-dd HH:mm"),
                          sumTimeAlarm = s.Select(c => c.Field<int>("sumTimeAlarm")).Sum(),

                          //HandleUser = string.Join(",", s.Select(c => c.Field<string>("HandleUser")?.ToString()).Where(t => !string.IsNullOrWhiteSpace(t))),
                          //HandleTime = string.Join(",", s.Select(c => c.Field<DateTime?>("HandleTime")?.ToString()).Where(t => !string.IsNullOrWhiteSpace(t)))

                          FaultType = s.Select(c => c.Field<string>("FaultType")).First(),

                          HandleInfo = s.Select(c => c.Field<string>("HandleInfo")).Where(t => !string.IsNullOrWhiteSpace(t)).FirstOrDefault(),
                          HandleUser = s.Select(c => c.Field<string>("HandleUser")).Where(t => !string.IsNullOrWhiteSpace(t)).FirstOrDefault(),
                          HandleTime = s.Select(c => c.Field<DateTime?>("HandleTime")).Where(t => t != null).FirstOrDefault(),
                          Count = s.Count()
                      };

            var item1Temp = res.Where(x => x.sumTimeAlarm > 0).OrderByDescending(x => x.Count).ToList();
            var item1 = from c in item1Temp
                        where c.Count > strCount
                        group c by new
                        {
                            EquipmentCode = c.EquipmentCode,
                            EquipmentName = c.EquipmentName,
                            eqType = c.eqType,
                            AlarmStatr = c.AlarmStatr,
                            AlarmEnd = c.AlarmEnd
                        } into s
                        select new
                        {
                            EquipmentCode = s.Select(c => c.EquipmentCode).First(),
                            EquipmentName = s.Select(c => c.EquipmentName).First(),
                            Alarm = s.Select(c => c.Alarm).First(),
                            eqType = s.Select(c => c.eqType).First(),
                            //AlarmStatr = s.Select(c => c.AlarmStatr).First(),
                            //AlarmEnd = s.Select(c => c.AlarmEnd).First(),
                            //sumTimeAlarm = s.Select(c => c.sumTimeAlarm).First(),
                            //FaultType = s.Select(c => c.FaultType).First(),
                            //HandleInfo = s.Select(c => c.HandleInfo).First(),
                            //HandleUser = s.Select(c => c.HandleUser).First(),
                            //HandleTime = s.Select(c => c.HandleTime).First(),
                            Count = s.Select(c => c.Count).First(),
                        };
            return new Tuple<dynamic, int>(item1, res.Count());

        }

        /// <summary>
        /// 月时间段超过40min故障报警信息
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="eqType"></param>
        /// <returns></returns>
        public DataSet GetFaultOverTime(string startTime,string endTime,string eqCode)
        {
            var sqlWhereMonth = $" where  t1.Created>='{startTime}' and t1.Created<='{endTime}' ";
            List<AreaClass> areaList = new List<AreaClass>();
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhereMonth += " and t1.EquipmentCode in ('{0}') ";

                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;
                }

                sqlWhereMonth = string.Format(sqlWhereMonth, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            string sql = @$" 

                        if Object_id('Tempdb..#listTime')   is not null   drop table #listTime;
                          select * into #listTime from (
                             select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated ,t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId 
                                  {sqlWhereMonth} and  datediff(minute, t1.created, t1.updated)>=1
                        )t;
                         ----------------<#listTime 处理相同设备在同年月日时分数据>----------------
                          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 #listTime e 
                                 )
                          delete from CTE WHERE  rownumber>1;  
                       ----------------------------------------------------------------------------
                       select * from (	
			 	                select EquipmentName=min(EquipmentName), Code=min(EquipmentCode) , Alarm=min(Alarm),EquipmentPropName=min(EquipmentPropName),
						                sumtimeCount= count(1), sumtime= min(timed)  
				                from(
				 	                select * ,timed=datediff(minute, created, updated)   from #listTime
				                )t
				                where t.timed>=40  
				                group by CONVERT(varchar(16),created, 20), CONVERT(varchar(16),updated, 20)
		                )ttt
		                 left join (
		                      select EquipmentCode ,HandleInfo =min(t1.HandleInfo),HandleUser=min(t1.HandleUser),Reason=min(t1.Reason)
			                  from equipmentalarmHandle  t1
		                      inner join equipmentalarm t2 on t1.EquipmentAlarmRecordId=t2.Id
		                     group by EquipmentCode	 
		                 )ttt2 on  ttt.Code=ttt2.EquipmentCode 
                          order by sumtime desc";
            var ds = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            return ds;
        }


        public DataTable GetSRMOverAlarmRecord(string startTime, string endTime, string eqCode, string alarm)
        {
            var startDate = startTime + " 00:00:01";
            var endDate = endTime + " 23:59:59";
            var sqlWhere = $" where  t1.Created>='{startDate}' and t1.Created<='{endDate}'";
            if (!string.IsNullOrEmpty(alarm))
            {
                sqlWhere += $"  and t1.alarm = '{alarm}' ";
            }

            List<AreaClass> areaList = new List<AreaClass>();
            string areaA = "";
            string areaL = "";
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhere += " and t1.EquipmentCode in ('{0}') ";

                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;

                    if (areaList[index].area == "A")
                    {
                        if (!string.IsNullOrEmpty(areaA))
                        {
                            areaA += ",";
                        }
                        areaA += areaList[index].value;
                    }

                    if (areaList[index].area == "L")
                    {
                        if (!string.IsNullOrEmpty(areaL))
                        {
                            areaL += ",";
                        }
                        areaL += areaList[index].value;
                    }
                }

                sqlWhere = string.Format(sqlWhere, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sql = GetSRMOverAlarmRecordSql(sqlWhere, areaList, alarm);
            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            return dt;
        }

        public string GetSRMOverAlarmRecordSql(string sqlWhere, List<AreaClass> areaList, string alarm)
        {
            var sql = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(sql))
                {
                    sql += " union all ";
                }
                if (!string.IsNullOrEmpty(alarm))
                {
                    sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            CONVERT(varchar,created,23) as date,
	                            EquipmentCode,
	                            COUNT(*) as count,
                                Alarm
                            --	sumTime = sum(DATEDIFF(minute, created, updated))
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
	                            Alarm,
	                            CONVERT(varchar,created,23) ";
                }
                else
                {
                    sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            CONVERT(varchar,created,23) as date,
	                            EquipmentCode,
	                            COUNT(*) as count
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
	                            CONVERT(varchar,created,23) ";
                }

                sql = string.Format(sql, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var resSql = @$"     
                         -----------------数据源--------------------------------------------
                          if Object_id('Tempdb..#tempdbRate') is not null drop table #tempdbRate;
                          select * into #tempdbRate from (
                                 select 
	                                    t1.Warehousecode,
	                                    t1.EquipmentCode,
	                                    t1.EquipmentName,
	                                    t1.EquipmentPropName,
	                                    t1.Alarm,
	                                    t1.Created,
	                                    t1.Updated,
	                                    t2.EquipmentTypeId,
	                                    t3.Code as EquipmentTypeCode,
	                                    t3.[Name] as EquipmentTypeName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code   and t1.equipmentCode like 'SRM%'
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId
                                  {sqlWhere} and DATEDIFF(minute, t1.created, t1.updated)>=1
                                  and t1.EquipmentPropCode in ('F8513','F8514','F8515','F8516','F8517','F8518','F8519','F8520','F8521')
                        )t;
                     -------------------处理相同设备同年月日时分故障------------------------------
                      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
                     --------------------时间段故障总结--------------------------
                        {sql}
                      ";
            return resSql;
        }

        /// <summary>
        /// 按时间段查询AGV设备异常频率记录（二维码点位维度）
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="eqCode"></param>
        /// <param name="alarm"></param>
        /// <returns></returns>
        public DataSet GetAGVCodePointAlarmRecord(string startTime, string endTime, string eqCode, string alarm)
        {
            DataSet ds = new DataSet();
            var startDate = startTime + " 00:00:01";
            var endDate = endTime + " 23:59:59";
            var sqlWhere = $" where  t1.Created>='{startDate}' and t1.Created<='{endDate}'";
            if (!string.IsNullOrEmpty(alarm))
            {
                sqlWhere += $"  and t1.alarm = '{alarm}' ";
            }

            List<AreaClass> areaList = new List<AreaClass>();
            string areaA = "";
            string areaL = "";
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhere += " and t1.EquipmentCode in ('{0}') ";

                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;

                    if (areaList[index].area == "A")
                    {
                        if (!string.IsNullOrEmpty(areaA))
                        {
                            areaA += ",";
                        }
                        areaA += areaList[index].value;
                    }

                    if (areaList[index].area == "L")
                    {
                        if (!string.IsNullOrEmpty(areaL))
                        {
                            areaL += ",";
                        }
                        areaL += areaList[index].value;
                    }
                }

                sqlWhere = string.Format(sqlWhere, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }
            var sql = GetAGVCodePointAlarmRecordSql(sqlWhere, areaList, alarm);
            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            ds.Tables.Add(dt);

            var sql2 = GetAGVBarCodesSql(areaList);
            var barCodeDt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql2);
            ds.Tables.Add(barCodeDt);

            ds.Tables[0].TableName = "result";
            ds.Tables[1].TableName = "codepoints";
            return ds;
        }

        public string GetAGVCodePointAlarmRecordSql(string sqlWhere, List<AreaClass> areaList, string alarm)
        {
            var sql = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(sql))
                {
                    sql += " union all ";
                }
                if (!string.IsNullOrEmpty(alarm))
                {
                    sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            EquipmentCode,
	                            COUNT(*) as count,
                                CodePoint,
                                Alarm
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
	                            Alarm,
                                CodePoint";
                }
                else
                {
                    sql += @"select
	                            name = '{0}',
	                            warehousecode,
	                            EquipmentCode,
                                CodePoint,
	                            COUNT(*) as count
                            from
	                            #tempdbRate t
                            where
	                            t.EquipmentCode in('{1}')
                            group by
	                            WarehouseCode,
	                            EquipmentCode,
                                CodePoint";
                }

                sql = string.Format(sql, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var resSql = @$"     
                         -----------------数据源--------------------------------------------
                          if Object_id('Tempdb..#tempdbRate') is not null drop table #tempdbRate;
                          select * into #tempdbRate from (
                                 select 
	                                    t1.Warehousecode,
	                                    t1.EquipmentCode,
	                                    t1.EquipmentName,
	                                    t1.EquipmentPropName,
	                                    t1.Alarm,
	                                    t1.Created,
	                                    t1.Updated,
	                                    t2.EquipmentTypeId,
	                                    t3.Code as EquipmentTypeCode,
	                                    t3.[Name] as EquipmentTypeName,
                                        t1.CodePoint
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId
                                  {sqlWhere} and DATEDIFF(minute, t1.created, t1.updated)>=1
                        )t;
                     -------------------处理相同设备同年月日时分故障------------------------------
                      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
                     --------------------时间段故障总结--------------------------
                        {sql}
                      ";
            return resSql;
        }

        /// <summary>
        /// 获取点位
        /// </summary>
        public string GetAGVBarCodesSql(List<AreaClass> area)
        {
            var label = area.First().label;
            var sql = $"select '{label}' as name, barcode  from rcs_steeringwheel_malaixiya_taiguang.dbo.t_base_map_point order by barcode";
            return sql;
        }

        /// <summary>
        /// 本周每天故障数据
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="eqCode">库别</param>
        /// <returns></returns>
        public DataSet GetEquipmentWeekDayFaultInfo(string startDay,string endDay,string eqCode)
        {
            DataSet ds;
            List<AreaClass> areaList = new List<AreaClass>();
            string areaA = "";
            string areaL = "";
            string startTime = startDay.TrimEnd()+" 00:00:00";
            string endTime = endDay.TrimEnd() + " 23:59:59";
            string sqlWhereWeek = @$" where  t1.Created>='{startTime}' and t1.Created<='{endTime}' ";
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhereWeek += " and t1.EquipmentCode in ('{0}') ";
                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);//拆分区域库别中的设备
                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;

                    if (areaList[index].area == "A")
                    {
                        if (!string.IsNullOrEmpty(areaA))
                        {
                            areaA += ",";
                        }
                        areaA += areaList[index].value;
                    }

                    if (areaList[index].area == "L")
                    {
                        if (!string.IsNullOrEmpty(areaL))
                        {
                            areaL += ",";
                        }
                        areaL += areaList[index].value;
                    }
                }
                sqlWhereWeek = string.Format(sqlWhereWeek, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sqlWeek = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                for(DateTime date = Convert.ToDateTime(startDay); date <= Convert.ToDateTime(endDay); date = date.AddDays(1))
                {
                    if (!string.IsNullOrEmpty(sqlWeek))
                    {
                        sqlWeek += " union all ";
                    }
                    var day = date.ToString("yyyy-MM-dd");
                    sqlWeek += @"select name = '{0}', warehousecode,                       --库别
                                        sumTime = sum(datediff(minute, created, updated)), --故障分
                                        today=MIN(CONVERT(VARCHAR(16),t.Created,23))       --日期
                                from  #listWeekFaultInfo t 
                                where  t.EquipmentCode in('{1}') and t.created>='{2} 00:00:00' and t.created<='{2} 23:59:59' 
	                            group by WarehouseCode";
                    sqlWeek = string.Format(sqlWeek, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)),day);
                }
            }
            string sql = @$"    -----------------本周公共数据源--------------------------------------------
                        if Object_id('Tempdb..#listWeekFaultInfo')   is not null   drop table #listWeekFaultInfo;
                          select * into #listWeekFaultInfo from (
                             select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated,t1.Id,
                                    t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId 
                                  {sqlWhereWeek} and  datediff(minute, t1.created, t1.updated)>=1
                        )t;
                         ---------------- #listWeekFaultInfo处理相同设备在同年月日时分数据>----------------
                          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 #listWeekFaultInfo e 
                                 )
                          delete from CTE WHERE  rownumber>1;  
                      -------本周所选设备每天故障分柱状图数据来源
                      {sqlWeek}
                      
                     ---------本周每天设备故障详情-------------
                     SELECT t.Id,
                            t.EquipmentCode,
                            t.EquipmentName,
                            t.Alarm,
                            sumtime=DATEDIFF(MINUTE, t.Created, t.Updated),
                            t.Created,t.Updated,
                            h.Reason,
                            h.HandleInfo,
                            h.HandleUser
                    FROM #listWeekFaultInfo t
                        LEFT JOIN dbo.equipmentalarmHandle h
                            ON h.EquipmentAlarmRecordId = t.Id
                    order by sumtime desc
                    drop table #listWeekFaultInfo;
                      ";
            ds = DALHelper.GetFreeSql().Ado.ExecuteDataSet(sql);
            return ds;
        }

        public DataTable GetEquipmentFaultReport(string startTime,string endTime, string eqCode)
        {
            var start = startTime+" 00:00:00";
            var end = endTime+" 23:59:59";
            var sqlWhere = $" where  t1.Created>='{start}' and t1.Created<='{end}' ";

            List<AreaClass> areaList = new List<AreaClass>();
            if (!string.IsNullOrEmpty(eqCode))
            {
                sqlWhere += " and t1.EquipmentCode in ('{0}') ";
                string code = "";
                areaList = JsonConvert.DeserializeObject<List<AreaClass>>(eqCode);

                for (var index = 0; index < areaList.Count; index++)
                {
                    if (!string.IsNullOrEmpty(code))
                    {
                        code += ",";
                    }
                    code += areaList[index].value;
                }
                sqlWhere = string.Format(sqlWhere, string.Join("','", code.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sql = GetSql(sqlWhere, areaList);
            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);

            return dt;
        }

        public string GetSql(string sqlWhere,List<AreaClass> areaList)
        {
            var sqlSelect = "";
            for (var index = 0; index < areaList.Count; index++)
            {
                if (!string.IsNullOrEmpty(sqlSelect))
                {
                    sqlSelect+= " union all ";
                }
                sqlSelect += @"select name = '{0}', warehousecode,
                                    sumTime = sum(datediff(minute, created, updated))
                            from  #list t 
                            where  t.EquipmentCode in('{1}') 
	                        group by WarehouseCode";
                sqlSelect = string.Format(sqlSelect, areaList[index].label, string.Join("','", areaList[index].value.Split(',', StringSplitOptions.RemoveEmptyEntries)));
            }

            var sql = @$"  
                        if Object_id('Tempdb..#list')   is not null   drop table #list;
                          select * into #list from (
                             select t1.warehousecode , t2.EquipmentTypeId,t1.created, t1.updated ,t1.EquipmentCode ,t1.Alarm,t1.EquipmentName,t1.EquipmentPropName
                              ,DATEDIFF(MINUTE,t1.created,t1.updated) as sumtime,t1.id as EquipmentAlarmId
	                              from equipmentalarm t1
	                              left join equipment t2 on t1.EquipmentCode=t2.code 
	                              left join  equipmenttype t3 on t3.id=t2.EquipmentTypeId 
                                  {sqlWhere} and  datediff(minute, t1.created, t1.updated)>=1
                        )t;
                         ----------------list 处理相同设备在同年月日时分数据>----------------
                          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 #list e 
                                 )
                          delete from CTE WHERE  rownumber>1;  
                        {sqlSelect}  order by sumTime desc";
            return sql;
        }

        public DataTable GetDeleteTaskInfo(string startDate, string endDate, string code)
        {
            var start = startDate + " 00:00:00";
            var end = endDate + " 23:59:59";
            var sql = $@"select*from V_DeleteTaskInfo where RecordTime >='{start}' 
                                     and RecordTime<='{end}'";
            if (!string.IsNullOrEmpty(code))
            {
                sql+= $@" and [Operator]='{code}'";
            }
            var dt = DALHelper.GetFreeSql().Ado.ExecuteDataTable(sql);
            return dt;
        }

    }

    public class AreaClass
    {
        public AreaClass() { }
        public string area
        {
            set; get;
        }
        public string label
        {
            set; get;
        }
        public string value
        {
            set; get;
        }
    }
}