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; 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); string user = sysWebUser.Account.ToString(); var projectRoseList = base.GetProjectRoleKeys(user); //Exel为ture就不分页,因为导出的话是全部导出 if (pageReq != null && !entity.Exel) { int total = 0; var list = query.ToOffsetPage(pageReq.page, pageReq.limit, ref total); result.Result = DataHandleProjectRole(list); result.Count = total; return result; } result.Result = DataHandleProjectRole(query.ToList()); result.Count = result.Result.Count; result.Message = 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; } /// <summary> /// 获取项目对应的角色 /// </summary> private List<base_project> DataHandleProjectRole(List<base_project> data) { var projectKeys = data.Select(item => item.keys).ToList(); var projectQuery = Context.Queryable<sys_role_projects_rel>() .Where(t => projectKeys.Contains(t.project_key)) .Select(t => new { t.project_key, t.project_roles_key }) .ToList(); //给项目 赋值 所有的角色 foreach (var item in data) { var projectUsers = projectQuery.Where(x => x.project_key == item.keys) .Select(x => x.project_roles_key) .ToArray(); item.projectRoles = 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 projectRoleKeys = GetProjectRoleKeys(user); exp.And(x => SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any()); } return exp.ToExpression();//拼接表达式 } catch (Exception ex) { throw new Exception($"{ex.Message}"); } } #endregion #region 项目列表 新增、编辑、删除、导出 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 newRoleKeys = entity.projectRoles.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(x => { _ = Guid.TryParse(x, out var key); return key; }).Where(x => x != Guid.Empty).ToList(); var rolesList = newRoleKeys.Select(x => new sys_role_projects_rel { keys = Guid.NewGuid(), project_key = entity.keys, project_roles_key = x, createTime = DateTime.Now, createBy = sysWebUser.Account }).ToList(); Context.Insertable(rolesList).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; } Context.Updateable(entity).AddQueue(); #region 项目角色 先全部删除在新增 var roles = entity.projectRoles.Split(",", StringSplitOptions.RemoveEmptyEntries); var rolesList = new List<sys_role_projects_rel>(); foreach (var item in roles) { Context.Deleteable<sys_role_projects_rel>().Where(x => x.project_key == entity.keys).AddQueue(); var temp = CreateRoleProjectsRel(entity, item); rolesList.Add(temp); } Context.Insertable(rolesList).AddQueue(); #endregion #region 项目角色 排查后删除在新增 /*var newRoleKeys = entity.projectRoles.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(x => { _ = Guid.TryParse(x, out var key); return key; }).Where(x => x != Guid.Empty).ToList(); var oldSysRoleProjectsRel = Context.Queryable<sys_role_projects_rel>().Where(x => x.project_key == entity.keys).ToList(); var oldRoleKeys = oldSysRoleProjectsRel.Select(x => x.project_roles_key).Distinct().ToList(); var addTemps = newRoleKeys.Except(oldRoleKeys); var removeTemps = oldRoleKeys.Except(newRoleKeys); Context.Deleteable<sys_role_projects_rel>().Where(x => x.project_key == entity.keys && removeTemps.Contains(x.project_roles_key)).AddQueue(); var rolesList = addTemps.Select(x => new sys_role_projects_rel { keys = Guid.NewGuid(), project_key = entity.keys, project_roles_key = x, createTime = DateTime.Now, createBy = sysWebUser.Account }).ToList(); Context.Insertable(rolesList).AddQueue(); */ #endregion var result = Context.SaveQueuesAsync().Result > 0; response.Status = result; if (!result) 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(); Context.Deleteable<sys_role_projects_rel>().Where(t => keysList.Contains(t.project_key)).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); } private sys_role_projects_rel CreateRoleProjectsRel(base_project entity, string roles_key) { return new sys_role_projects_rel { keys = Guid.NewGuid(), project_key = entity.keys, project_roles_key = Guid.Parse(roles_key), createTime = DateTime.Now, createBy = sysWebUser.Account }; } #endregion #region 项目地图 首页 public dynamic GetProjectMapList() { return ExceptionsHelp.Instance.ExecuteT(() => { var result = new Response(); var expression = GetProjectMapListWhere(); var list = Context.Queryable<base_project>().Where(expression).ToList(); var sumEqCount = new Dictionary<string, int>(); var sumEqRunCount = new Dictionary<string, int>(); var sumEqFreeCountCount = new Dictionary<string, int>(); 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;//关联项目 总设备数量 if (!sumEqCount.ContainsKey(item.projectCode)) sumEqCount.Add(item.projectCode, i.eqCount); if (!sumEqRunCount.ContainsKey(item.projectCode)) sumEqRunCount.Add(item.projectCode, i.eqRunCount); if (!sumEqFreeCountCount.ContainsKey(item.projectCode)) sumEqFreeCountCount.Add(item.projectCode, 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是有未处理报警 } decimal tempSumEqCount = sumEqCount.Values.Sum(); //返回 项目数据、总设备数、在线率、是否有报警、是否有待保养 string onlineRate = "0%"; if (tempSumEqCount > 0) { decimal onlineRateFormatted = Math.Round((sumEqRunCount.Values.Sum() + sumEqFreeCountCount.Values.Sum()) / tempSumEqCount, 2); onlineRate = (onlineRateFormatted * 100m).ToString("0.##") + "%"; } result.Result = new { list = list, sumEqCount = tempSumEqCount, onlineRate = onlineRate }; return result; }); } public Expression<Func<base_project, bool>> GetProjectMapListWhere() { try { var exp = Expressionable.Create<base_project>(); //非管理员,查询登陆用户绑定客户管理的项目 string currentUser = sysWebUser.Account; if (currentUser != SystemVariable.DefaultCreated) { var projectRoleKeys = GetProjectRoleKeys(currentUser); exp.And(x => SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any()); } 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; }); } /// <summary> /// 获取项目编号信息 /// </summary> /// <returns></returns> public Response GetProjectInfo() { return ExceptionsHelp.Instance.ExecuteT(() => { var response = new Response(); var result = Context.Queryable<bus_sn_project_info_wms_head>().Where(x => !string.IsNullOrEmpty(x.correlatedCode) && !SqlFunc.Subqueryable<base_project>().Where(s => s.projectCode == x.correlatedCode).Any()).Select(x => x.correlatedCode).Distinct().ToList(); //var result = Enumerable.Range(0, 3).Select(x => Guid.NewGuid()).ToList(); response.Result = result; response.Count = result.Count; return response; }); } #endregion } }