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;

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.otherCode == 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 != default && (currentTime - x.updateTime).TotalMinutes <= 5);

                    i.eqFreeCount = eqStatus.Count((x) => x.projectKeys == i.keys && x.status == EquipmentStatus.Free.ToString()

                                                            && x.updateTime != default && (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 != default && (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<bus_equipment_maintain_record_detail>().Where(x => x.projectKeys == i.keys && x.status == (int)EnumMaintainStatus.ToBeMaintain && x.nextMaintainTime <= DateTime.Now).Any();//返回true是有待保养
                    var isHaveAlarm = Context.Queryable<daq_equipment_alarm_record>().Where(t => t.equipmentCode == SqlFunc.Subqueryable<base_equipment>().Where(b => b.projectKeys == i.keys).Select(b => b.otherCode)).Any();
                    i.isHaveAlarm = isHaveAlarm;//返回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

    }

}