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 ApkInfo;
using Microsoft.AspNetCore.Http;
using System.IO;
using System.Threading;
using Hh.Mes.Common.config;
using System.Data;
using Hh.Mes.POJO.EnumEntitys;
using System.Text;

namespace Hh.Mes.Service.Configure
{
	public class BaseProjectService : RepositorySqlSugar<base_project>
	{
		/// <summary>
		/// 项目列表
		/// </summary>
		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;
					result.Result = query.ToOffsetPage(pageReq.page, pageReq.limit, ref total);
					result.Count = total;
					return result;
				}
				result.Result = query.ToList();
				result.Count = result.Result.Count();
				return result;
			}, catchRetrunValue: "list");
		}


		/// <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 sumEqOnlineCount = 0;
				decimal sumEqFreeCountCount = 0;
				var equipment = Context.Queryable<base_equipment>()
									   .Where(x => !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode)).ToList();
				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 = equipment.Where(x => x.projectKeys == i.keys).Count();//单个项目设备数
					i.eqOnLineCount = 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.eqOnLineCount - i.eqFreeCount - i.eqFailureCount;

					sumEqCount += i.eqCount;//关联项目 总设备数量
					sumEqOnlineCount += i.eqOnLineCount;
					sumEqFreeCountCount += i.eqFreeCount;
				}
				//返回 项目数据、总设备数、在线率、妥善率
				string onlineRate = "0%";
				if (sumEqCount > 0)
				{
					decimal onlineRateFormatted = Math.Round((sumEqOnlineCount + 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 factory = new base_factory
				{
					keys = Guid.NewGuid(),
					factoryCode = "fac_" + entity.projectCode,
					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.equipmentCode == 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.eqOnLineCount = 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.eqOnLineCount - po.eqStandByCount - po.eqErrorCount;

				//设备运行（当天）：故障时间、运行时间、待机时间、在线率
				decimal d = 0;
				if (po.eqSumCount > 0)
				{
					d = (decimal)(po.eqOnLineCount + 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");
		}

		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 ON t1.equipmentCode = 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");
		}

		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.equipmentCode).ToList();
				var alarms = Context.Queryable<daq_equipment_alarm_record>()
										.Where(i => i.createTime >= startDateTime && i.createTime <= endDateTime && eqInfo.Contains(i.equipmentCode))
										.ToList();
				var alarmInfo = alarms.GroupBy(i => new
				{
					Date = i.createTime?.Date,//按（年月日）日期排序
					i.equipmentCode,
					i.equipmentName
				}).ToList();
				//计算故障总时间
				var failureTime = alarmInfo.Select(i => new
				{
					i.Key.equipmentCode,
					i.Key.equipmentName,
					Date = i.Key.Date?.ToString("yyyy-MM-dd"),
					sumTime = i.Sum(t => t.errorduration)
				}).ToList();
				//计算故障次数 
				var failureCount = alarms.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 failureOverFourty = Context.Queryable<daq_equipment_alarm_record>()
										.Where(i => i.createTime >= startDateTime && i.createTime <= endDateTime
												 && eqInfo.Contains(i.equipmentCode) && i.errorduration > 40 * 60)
										.OrderBy(i =>new { i.equipmentCode ,i.errorduration },OrderByType.Desc)
										.Select(i => new
										{
											i.equipmentCode,
											i.equipmentName,
											i.alarmMessage,
											i.createTime,
											i.updateTime,
											i.errorduration,
											i.handleTime,
											i.remark
										})
										.ToList();
				//var failureCountList= failureCount.AsQueryable().OrderBy(x=>new { x.equipmentCode,x.count},OrderByType.Desc).ToList();

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

		} 
		#endregion
	}
}