sys/Hh.Mes.Service/WebService/Configure/BaseProjectService.cs

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;
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();
                //用户-客户 客户关联的项目
                string user = sysWebUser.Account;
                if (user != SystemVariable.DefaultCreated)//非管理员，查询登陆用户绑定客户管理的项目
                {
                    sys_user_client_rel clientKeys = Context.Queryable<sys_user_client_rel>().Where(x => x.userAccount == user).First();
                    if (clientKeys != null)
                    {
                        List<Guid> projectKeysList = Context.Queryable<base_project_client_rel>().Where(x => x.clientKeys == clientKeys.clientKeys).Select(x => x.projectKeys).ToList();
                        entity.projectKeysString = string.Join(",", projectKeysList);
                    }
                }
                var expression = LinqWhere(entity);
                //先组合查询表达式
                var query = Context.Queryable<base_project>().Where(expression);
                if (string.IsNullOrEmpty(entity.projectKeysString) && user != SystemVariable.DefaultCreated)
                {
                    //非管理员，且无关联项目
                    query = Context.Queryable<base_project>().Where(x => false);
                }
                //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, base_project_client_rel, sys_user_client_rel, base_client_info>((pro, proClient, uClient, client) =>
                                                new JoinQueryInfos(JoinType.Left, pro.keys == proClient.projectKeys,
                                                    JoinType.Left, proClient.clientKeys == uClient.clientKeys,
                                                    JoinType.Left, proClient.clientKeys == client.keys))
                                     .Where((pro, proClient, uClient, client) => projectKeys.Contains(pro.keys))
                                     .Select((pro, proClient, uClient, client) => new
                                     {
                                         pro.keys,
                                         uClient.userAccount,
                                         client.clientName
                                     })
                                     .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);
                var projectClientNames = projectQuery.Where(x => x.keys == item.keys)
                                                 .Select(x => x.clientName)
                                                 .Distinct()
                                                 .ToArray();
                item.projectClientNames = string.Join(",", projectClientNames);
            }
            return data;
        }
        #endregion
        /// <summary>
        /// 项目地图 首页
        /// </summary>
        /// <returns></returns>
        public dynamic GetProjectMapList()
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                string currentUser = sysWebUser.Account;
                var list = Context.Queryable<base_project>().ToList();
                if (SystemVariable.DefaultCreated != currentUser)
                {
                    //读取当前用户下面所有的设备
                    var currentUserDevice = base.GetCurrentUserAllDevice();
                    var projectKeysList = currentUserDevice.Select(t => t.projectKeys).ToList();
                    list = list.Where(t => projectKeysList.Contains(t.keys)).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;
                }
                //返回 项目数据、总设备数、在线率、妥善率
                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,
                    tuoShanRate = "20%"
                };
                return result;
            });
        }
        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;
                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;
                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<base_project_client_rel>().Any(x => keysList.Contains(x.projectKeys));
                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);
        }
        public Expression<Func<base_project, bool>> LinqWhere(base_project model)
        {
            try
            {
                var exp = Expressionable.Create<base_project>();
                //数据过滤条件
                //if (!string.IsNullOrWhiteSpace(model.XXX)) exp.And(x => x.XXX.Contains(model.XXX));
                if (!string.IsNullOrEmpty(model.projectKeysString))
                {
                    model.projectKeysString.Split(",").ToList()
                        .ForEach(i => exp.Or(x => x.keys == new Guid(i)));
                }
                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));
                }
                return exp.ToExpression();//拼接表达式
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        }
        #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(-7).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");
        }
        #endregion
    }
}