MES / IOT · Files

using Hh.Mes.Common.Infrastructure;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Hh.Mes.Pojo.System;
using System.Data;
using Hh.Mes.POJO.EnumEntitys;
using System.Text;
using Hh.Mes.POJO.ViewModel.Project;
using NPOI.POIFS.FileSystem;

namespace Hh.Mes.Service.Configure
{
    public class BaseProjectService : RepositorySqlSugar<base_project>
    {
        #region 项目列表
        public dynamic Load(PageReq pageReq, base_project entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();                
                var expression = LinqProjectWhere(entity);
                //先组合查询表达式
                var query = Context.Queryable<base_project>()
                                   .Where(expression);

                //Exel为ture就不分页，因为导出的话是全部导出
                if (pageReq != null && !entity.Exel)
                {
                    int total = 0;
                    var list = query.ToOffsetPage(pageReq.page, pageReq.limit, ref total);
                    result.Result = DataHandle(list);
                    result.Count = total;
                    return result;
                }
                result.Result = DataHandle(query.ToList());
                result.Count = result.Result.Count;
                return result;
            }, catchRetrunValue: "list");
        }

        private List<base_project> DataHandle(List<base_project> data)
        {
            var projectKeys = data.Select(item => item.keys).ToList();
            var projectQuery = Context.Queryable<base_project, sys_user_project_rel>((pro, upRel) =>
                                                new JoinQueryInfos(JoinType.Left, pro.keys == upRel.clientKeys))
                                     .Where((pro, upRel) => projectKeys.Contains(pro.keys))
                                     .Select((pro, upRel) => new
                                     {
                                         pro.keys,
                                         upRel.userAccount
                                     })
                                     .ToList();
            //给项目 赋值 登入用户、项目关联绑定客户信息
            foreach (var item in data)
            {
                var projectUsers = projectQuery.Where(x => x.keys == item.keys)
                                               .Select(x => x.userAccount)
                                               .ToArray();
                item.projectUsers = string.Join(",", projectUsers);                    
            }

            return data;
        }

        public Expression<Func<base_project, bool>> LinqProjectWhere(base_project model)
        {
            try
            {
                var exp = Expressionable.Create<base_project>();
                //数据过滤条件
                if (!string.IsNullOrWhiteSpace(model.projectName))
                {
                    exp.And(x => x.projectName.Contains(model.projectName));
                }
                if (!string.IsNullOrWhiteSpace(model.projectAddress))
                {
                    exp.And(x => x.projectAddress.Contains(model.projectAddress));
                }
                if (!string.IsNullOrWhiteSpace(model.projectManager))
                {
                    exp.And(x => x.projectManager.Contains(model.projectManager));
                }
                //非管理员，查询登陆用户绑定客户管理的项目
                string user = sysWebUser.Account;
                if (user != SystemVariable.DefaultCreated)
                {
                    var projectKeys = Context.Queryable<sys_user_project_rel>().Where(x => x.userAccount == user)
                                            .Select(x => x.clientKeys)
                                            .ToList();
                    if (projectKeys.Count > 0) exp.And(x => projectKeys.Contains(x.keys));
                }
                return exp.ToExpression();//拼接表达式
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        }
        #endregion

        public dynamic Ins(base_project entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                var projectKey = Guid.NewGuid();
                entity.keys = projectKey;
                entity.createBy = sysWebUser.Account;
                entity.createTime = DateTime.Now;

                if (entity.projectClientInfoKeys!=Guid.Empty)
                {
                   var clientName= Context.Queryable<base_project_client_info>().First(x => x.keys == entity.projectClientInfoKeys).clientName;
                    entity.projectClientName = clientName;
                }

                Context.Insertable(entity).AddQueue();

                //厂房编码=项目编码
                var factoryCount = Context.Queryable<base_factory>().Where(x => x.projectKeys == entity.keys).Count() + 1;
                var factoryCode = entity.projectCode + factoryCount.ToString().PadLeft(2, '0');
                var factory = new base_factory
                {
                    keys = Guid.NewGuid(),
                    factoryCode = factoryCode,
                    factoryName = entity.projectName + "默认一厂",
                    createBy = sysWebUser.Account,
                    createTime = DateTime.Now,
                    projectKeys = projectKey
                };
                Context.Insertable(factory).AddQueue();
                var result = Context.SaveQueuesAsync().Result > 0;
                response.Status = result;
                if (!result) response.Message = SystemVariable.dataActionError;
                return response;
            });
        }

        public dynamic Upd(base_project entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                entity.updateBy = sysWebUser.Account;
                entity.updateTime = DateTime.Now;
                if (entity.projectClientInfoKeys != Guid.Empty)
                {
                    var clientName = Context.Queryable<base_project_client_info>().First(x => x.keys == entity.projectClientInfoKeys).clientName;
                    entity.projectClientName = clientName;
                }
                response.Status = Update(entity);
                if (!response.Status) response.Message = SystemVariable.dataActionError;
                return response;
            });
        }

        public dynamic DelByIds(Guid[] keysList)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                //1.1删除项目需要判断绑定客户
                var isBindClient = Context.Queryable<sys_user_project_rel>().Any(x => keysList.Contains(x.clientKeys));
                if (isBindClient)
                {
                    return response.ResponseError("选中删除的项目已关联绑定客户，不能直接删除，请选取消项目关联客户！");
                }
                //1.2 项目是否绑定设备 通过设备绑定是否存在projectKeys
                //var isBindEq = Context.Queryable<base_equipment>().Any(x => keysList.Contains(x.projectKeys));
                //if (isBindEq)
                //{
                //    return response.ResponseError("选中删除的项目已关联绑定设备，不能直接删除，请删除设备后在删除项目！");
                //}
                Context.Deleteable<base_project>(t => keysList.Contains(t.keys)).AddQueue();
                Context.Deleteable<base_factory>(t => keysList.Contains(t.projectKeys)).AddQueue();

                var result = Context.SaveQueues() > 0;
                response.Status = result;
                if (!result) response.Message = SystemVariable.dataActionError;
                return response;
            });
        }

        public Response ExportData(base_project entity)
        {
            return Load(null, entity);
        }

        #region  项目地图 首页
        public dynamic GetProjectMapList()
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                var expression = GetProjectMapListWhere();
                var list = Context.Queryable<base_project, sys_user_project_rel>((pro,upRel) =>
                                                           new JoinQueryInfos(JoinType.Left, pro.keys == upRel.clientKeys ))
                                  .Where(expression)
                                  .ToList();

                decimal sumEqCount = 0;
                decimal sumEqRunCount = 0;
                decimal sumEqFreeCountCount = 0;

                var eqStatus = Context.Queryable<base_equipment, daq_equipment_status_record>(
                                              (x, y) => new JoinQueryInfos(JoinType.Inner, x.equipmentCode == y.equipmentCode))
                                      .Where((x, y) => !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode))
                                      .Select((x, y) => new
                                      {
                                          x.projectKeys,
                                          y.updateTime,
                                          y.status
                                      })
                                      .ToList();
                var currentTime = DateTime.Now;
                foreach (var item in list)
                {
                    var i = item; // 将每个元素赋值给一个新的变量 i
                    i.eqCount = item.eqCount;//单个项目设备数
                    i.eqRunCount = eqStatus.Count((x) => x.projectKeys == i.keys && x.status == EquipmentStatus.Running.ToString()
                                                            && x.updateTime != null && (currentTime - x.updateTime).TotalMinutes <= 5);

                    i.eqFreeCount = eqStatus.Count((x) => x.projectKeys == i.keys && x.status == EquipmentStatus.Free.ToString()
                                                            && x.updateTime != null && (currentTime - x.updateTime).TotalMinutes <= 5);
                    //故障数
                    i.eqFailureCount = eqStatus.Count((x) => x.projectKeys == i.keys && (x.status == EquipmentStatus.Failure.ToString() ||
                                                                                         x.status == EquipmentStatus.Error.ToString())
                                                          && x.updateTime != null && (currentTime - x.updateTime).TotalMinutes <= 5);
                    i.eqOfflineCount = i.eqCount - i.eqRunCount - i.eqFreeCount - i.eqFailureCount;

                    sumEqCount += i.eqCount;//关联项目 总设备数量
                    sumEqRunCount += i.eqRunCount;
                    sumEqFreeCountCount += i.eqFreeCount;

                    //查询每个项目下是否有报警未处理和是否有待保养
                    i.isHaveMaintain = Context.Queryable<base_equipment>().Any(x => x.projectKeys == i.keys && x.isMaintain);//返回true是有待保养
                    i.isHaveAlarm = Context.Queryable<base_equipment>().Any(x => x.projectKeys == i.keys && x.remark.Contains("有未处理报警记录"));//返回true是有未处理报警
                }
                //返回 项目数据、总设备数、在线率、是否有报警、是否有待保养
                string onlineRate = "0%";
                if (sumEqCount > 0)
                {
                    decimal onlineRateFormatted = Math.Round((sumEqRunCount + sumEqFreeCountCount) / sumEqCount, 2);
                    onlineRate = (onlineRateFormatted * 100m).ToString("0.##") + "%";
                }
                result.Result = new
                {
                    list = list,
                    sumEqCount = sumEqCount,
                    onlineRate = onlineRate
                };
                return result;
            });
        }

        public Expression<Func<base_project, sys_user_project_rel, bool>> GetProjectMapListWhere()
        {
            try
            {
                var exp = Expressionable.Create<base_project, sys_user_project_rel>();
                //非管理员，查询登陆用户绑定客户管理的项目
                string currentUser = sysWebUser.Account;
                if (currentUser != SystemVariable.DefaultCreated)
                {
                    exp.And((pro, upRel) => upRel.userAccount == currentUser);
                }
                return exp.ToExpression();//拼接表达式
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        } 
        #endregion

        #region 项目概述

        public dynamic LoadProjectOverview(base_project entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                //项目地图调用  返回结果
                project_overview po = new project_overview();

                //设备概述
                po.eqSumCount = Context.Queryable<base_equipment>()
                                       .Where(x => x.projectKeys == entity.keys && !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode))
                                       .ToList().Count;//单个项目 设备总数
                var eqInfo = Context.Queryable<base_equipment, daq_equipment_status_record>(
                                           (x, y) => new JoinQueryInfos(JoinType.Inner, x.otherCode == y.equipmentCode))
                                         .Where((x, y) => x.projectKeys == entity.keys && SqlFunc.SqlServer_DateDiff("Minute", y.updateTime, DateTime.Now) <= 5 &&
                                                       !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode))
                                         .Select((x, y) => new { y.status })
                                         .ToList();
                po.eqRunCount = eqInfo.Where(i => i.status == EquipmentStatus.Running.ToString()).ToList().Count;//在线数
                po.eqStandByCount = eqInfo.Where(i => i.status == EquipmentStatus.Free.ToString()).ToList().Count;//待机数
                po.eqErrorCount = eqInfo.Where(i => i.status == EquipmentStatus.Failure.ToString() || i.status == EquipmentStatus.Error.ToString()).ToList().Count;//故障数
                po.eqOffLineCount = po.eqSumCount - po.eqRunCount - po.eqStandByCount - po.eqErrorCount;

                //设备运行（当天）：故障时间、运行时间、待机时间、在线率
                decimal d = 0;
                if (po.eqSumCount > 0)
                {
                    d = (decimal)(po.eqRunCount + po.eqStandByCount) / po.eqSumCount;//在线数÷总设备数
                }
                string onlineRate = string.Format("{0:P2}", Math.Round(d, 4));//在线率
                po.runningRate = onlineRate == "0.00%" ? "0%" : onlineRate;
                result.Result = po;
                return result;
            }, catchRetrunValue: "list");
        }

        /// <summary>
        /// 设备在线状况
        /// </summary>
        /// <param name="projectKeys"></param>
        /// <returns></returns>
        public dynamic GetEqInfoByProjectKeys(Guid projectKeys)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                var stringBuilder = new StringBuilder();
                stringBuilder.AppendLine($@"SELECT t1.equipmentCode, t1.equipmentName, t2.Status, t2.UpdateTime,
												   seconds = ISNULL(DATEDIFF(second, t2.UpdateTime, GETDATE()), -1)
										    FROM base_equipment t1
										    LEFT JOIN daq_equipment_status_record t2 WITH(NOLOCK) ON t1.otherCode = t2.EquipmentCode");
                stringBuilder.AppendFormat("WHERE t1.projectKeys = '{0}' ", projectKeys);

                string notInCondition = $"AND t1.equipmentTypeCode NOT IN ('{SystemVariable.IotNotContainDevice}')";
                stringBuilder.AppendLine(notInCondition);

                stringBuilder.AppendLine(" order by  t1.equipmentTypeCode, t2.Status ");


                string sqlQuery = stringBuilder.ToString();
                var dt = base.Context.Ado.GetDataTable(stringBuilder.ToString());
                result.Result = dt;
                result.Count = dt == null ? 0 : dt.Rows.Count;
                return result;
            }, catchRetrunValue: "list");
        }

        /// <summary>
        /// 设备故障总时间、设备故障发生次数、设备超40分钟报警
        /// </summary>
        /// <param name="projectKeys"></param>
        /// <returns></returns>
        public dynamic GetFailureList(Guid projectKeys)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                DateTime date = DateTime.Now;
                DateTime startDateTime = date.AddDays(-6).Date;//获取第前7天时间
                DateTime endDateTime = DateTime.Parse(date.ToString("yyyy-MM-dd") + " 23:59:59");//获得当天时间
                                                                                                 //计算故障时间
                var eqInfo = Context.Queryable<base_equipment>()
                                    .Where(i => i.projectKeys == projectKeys && i.equipmentTypeCode != "StationMonitor")
                                    .Select(i => i.otherCode).ToList();
                var alarmsList = Context.Queryable<daq_equipment_alarm_record>()
                                        .Where(i => i.createTime >= startDateTime && i.createTime <= endDateTime && eqInfo.Contains(i.equipmentCode))
                                        .ToList();
                var alarmInfo = alarmsList.GroupBy(i => new
                {
                    Date = i.createTime.Date.ToString("yyyy-MM-dd"),//按（年月日）日期排序
                    i.equipmentCode,
                    i.equipmentName
                }).ToList();

                //计算故障总时间
                var failureTime = alarmInfo.Select(i => new
                {
                    i.Key.equipmentCode,
                    i.Key.equipmentName,
                    Date = i.Key.Date,
                    sumTime = i.Sum(t => t.errorduration)
                }).ToList();
                //计算故障次数 
                var failureCount = alarmsList.GroupBy(i => new
                {
                    i.equipmentCode,
                    i.equipmentName
                }).Select(i => new
                {
                    i.Key.equipmentCode,
                    i.Key.equipmentName,
                    count = i.Count()
                })
                .OrderByDescending(i => i.count)
                .ToList();

                //计算设备故障（超40分钟）
                var min40 = 40 * 60;
                var failureOverFourty = alarmsList.Where(i => i.errorduration > min40)
                                        .OrderByDescending(x => x.equipmentCode).ThenByDescending(x => x.errorduration)
                                        .Select(i => new
                                        {
                                            i.equipmentCode,
                                            i.equipmentName,
                                            i.alarmMessage,
                                            i.createTime,
                                            i.updateTime,
                                            i.errorduration,
                                            i.handleTime,
                                            i.remark
                                        })
                                        .ToList();

                result.Result = new
                {
                    failureTime = failureTime,//设备故障总时间  每台设备妥善率
                    failureCount = failureCount,//故障发生次数
                    failureOverFourty = failureOverFourty//超40分钟报警
                };
                return result;
            }, catchRetrunValue: "list");

        }


        /// <summary>
        /// 获取近七天设备开机时间
        /// </summary>
        /// <param name="projectKeys"></param>
        /// <returns></returns>
        public dynamic GetEqPowerOnTime(Guid projectKeys)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
				var date = DateTime.Now;
				var startDateTime = date.AddDays(-6);
				var endDateTime = DateTime.Parse(date.ToString("yyyy-MM-dd") + " 23:59:59");
				var eqList = Context.Queryable<base_equipment>().Where(x => x.projectKeys == projectKeys && !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode))
                                    .Select(x => new { x.equipmentCode, x.equipmentName })
                                    .ToList();
                if (eqList.Count==0) return response.ResponseError(SystemVariable.queryNotData);

				//设备编码集合
				var equipmentCodeList = eqList.Select(t => t.equipmentCode).ToList();
				var eqRecordHistoryList = Context.Queryable<daq_equipment_status_record_history>()
                                                 .Where(x => equipmentCodeList.Contains(x.equipmentCode) && x.createTime >= startDateTime
                                                                                                        && x.createTime <= endDateTime)
                                                 .Select(x => new { x.equipmentCode, x.createTime,x.statusDuration })
                                                 .ToList();

				var result = new List<ProjectPowerOnDto>();
				foreach (var e in eqList)
				{
					for (int i = -6; i <= 0; i++)
					{
						var d = new ProjectPowerOnDto();
						d.EquipmentCode = e.equipmentCode;
						d.EquipmentName = e.equipmentName;
						d.Date = date.AddDays(i).Date;
						DateTime endTime = DateTime.Parse(d.Date.ToString("yyyy-MM-dd") + " 23:59:59");

                        var PowerOnTime = eqRecordHistoryList.Where(x => x.equipmentCode == e.equipmentCode && x.createTime >= d.Date && x.createTime <= endTime)
                                                            .Sum(x => x.statusDuration);
						d.PowerOnTime = Math.Round(TimeSpan.FromSeconds(PowerOnTime).TotalHours, 2);

                        //d.PowerOnTimeSeconds = TimeSpan.FromSeconds(PowerOnTime).TotalSeconds;
                        result.Add(d);
					}

				}
				response.Result = result;
                return response;
            });
        }
        #endregion
    }
}