using Microsoft.Extensions.Primitives; using Newtonsoft.Json; using Ropin.Inspection.Model; using Ropin.Inspection.Model.Entities; using Ropin.Inspection.Model.ViewModel.DEV; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Ropin.Inspection.Repository { public class TmtnPushMsgResultRepository : RepositoryBase, ITmtnPushMsgResultRepository { public TmtnPushMsgResultRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task UpdateStatus(string msgCode, string Status,string userId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("msgCode", msgCode), new MySqlConnector.MySqlParameter("status", Status), new MySqlConnector.MySqlParameter("userId", userId) }; string sql = $" UPDATE TMTN_PushMsgResult SET C_Status = @status WHERE (C_MessageCode =@msgCode) AND (C_PushMsgToCode=@userId); "; int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters); bool result = iResult > 0; return Task.FromResult(result); } public Task> GetConditionAsync(TmtnPushMsgResultSearchModel searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("PushMsgToCode", searchModel.C_PushMsgToCode), new MySqlConnector.MySqlParameter("MsgTypeCode", searchModel.C_MsgTypeCode), new MySqlConnector.MySqlParameter("status", searchModel.C_Status), new MySqlConnector.MySqlParameter("generationType", searchModel.I_GenerationType), new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Content+"%"), new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode), new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("Id ", searchModel.C_ID?.ToString()), new MySqlConnector.MySqlParameter("start ", searchModel.D_Start?.ToString("yyyy-MM-dd")), new MySqlConnector.MySqlParameter("end ", searchModel.D_End?.ToString("yyyy-MM-dd")) }; StringBuilder sql = new StringBuilder(); sql.Append(@"select * from ( select p.*,d.C_StoreCode,b.C_Name as C_MsgTypeName ,m.I_GenerationType,m.I_MsgStatus, (select COUNT(1) from TMTN_AlarmShadowRecord where C_PushMsgResultCode=p.C_ID) as IsVideo from TMTN_PushMsgResult p LEFT JOIN TDEV_DevStore d on (p.C_DevStoreCode=d.C_ID) LEFT JOIN TBDM_CodeDetail b on (b.C_Code=p.C_MsgTypeCode) LEFT JOIN TSYS_Message m on (p.C_MessageCode=m.C_ID) )tab where 1=1"); if (!string.IsNullOrEmpty(searchModel.C_PushMsgToCode)) { sql.Append(" AND C_PushMsgToCode=@PushMsgToCode "); } if (!string.IsNullOrEmpty(searchModel.C_Status)) { sql.Append(" and C_Status=@status "); } if (!string.IsNullOrEmpty(searchModel.C_MsgTypeCode)) { sql.Append($" and (C_MsgTypeCode=@MsgTypeCode or C_Content like '%\"C_MsgTypeCode\":\"{searchModel.C_MsgTypeCode}\"%') "); } if (searchModel.MsgTypeList!=null&&searchModel.MsgTypeList.Count>0) { StringBuilder contsql= new StringBuilder(); foreach (var item in searchModel.MsgTypeList) { contsql.Append($" C_Content like '%\"C_MsgTypeCode\":\"{item}\"%' or "); } sql.Append($" and ({contsql.ToString()} C_MsgTypeCode in ('{String.Join("','", searchModel.MsgTypeList)}') ) "); } if (!string.IsNullOrEmpty(searchModel.C_ID)) { sql.Append(" and C_ID = @Id "); } if (!string.IsNullOrEmpty(searchModel.C_Content)) { sql.Append(" and C_Content like @name "); } if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode)) { sql.Append(" and C_DevStoreCode=@DevStoreCode"); } if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql.Append(" and C_StoreCode=@StoreCode"); } if (searchModel.I_GenerationType!=null) { sql.Append(" and I_GenerationType=@generationType "); } if (searchModel.MsgStatus != null&& searchModel.MsgStatus.Count>0) { string msgStarus=string.Join(",", searchModel.MsgStatus); sql.Append($" and I_MsgStatus in ({msgStarus}) "); } if (searchModel.D_Start != null && searchModel.D_End != null) { sql.Append(" and date_format(D_CreateOn,'%Y-%m-%d') BETWEEN @start AND @end "); } //sql.Append(" group by C_PushMsgToCode,C_Content,C_DevStoreCode "); sql.Append(" order by D_CreateOn desc "); IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); return Task.FromResult(recordItemlist); } /// /// 消息报警天数计量 /// /// /// public Task GetPushMsgResultContentAsync(TmtnPushMsgResultSearchModel searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("MsgTypeCode", searchModel.C_MsgTypeCode), new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Content+"%"), new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode), new MySqlConnector.MySqlParameter("start ", searchModel.D_Start), }; StringBuilder sql = new StringBuilder(); sql.Append($"select C_Content,D_CreateOn from TMTN_PushMsgResult where C_Content like '%\"C_MsgTypeCode\":\"MSG_TYPE_011\",\"Subject\":\"设备开启关闭报警\"%' "); if (!string.IsNullOrEmpty(searchModel.C_Content)) { sql.Append(" and C_Content like @name "); } if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode)) { sql.Append(" and C_DevStoreCode=@DevStoreCode"); } if (searchModel.D_Start != null) { sql.Append(" and D_CreateOn> @start "); } sql.Append(" order by D_CreateOn asc "); IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); DateTime? time1=null, time2 = null; int day = 0; int Hour = 0;int minutes=0; int dayTotal = 0; int HourTotal = 0; int minutesTotal = 0; int dayStop = 0; int HourStop = 0; int minutesStop = 0; int dayStopTotal = 0; int HourStopTotal = 0; int minutesStopTotal = 0; string FistTime=null,LastTime=null; foreach (var item in recordItemlist) { if (item!=null) { try { var msgMode = JsonConvert.DeserializeObject(item.C_Content); if (msgMode.Msg == "开启") { time1 = string.IsNullOrEmpty(msgMode.CreateOn) ? null : Convert.ToDateTime(msgMode.CreateOn); if (time2 != null && time1 != null) { int dateCompare = DateTime.Compare(time1.Value, time2.Value); if (dateCompare > 0)//time2小于time1 { TimeSpan diff = (time1.Value).Subtract(time2.Value); dayStop = diff.Days; HourStop = diff.Hours; minutesStop = diff.Minutes; minutesStopTotal = minutesStopTotal + minutesStop; if (minutesStopTotal >= 60) { HourStopTotal++; minutesStopTotal = minutesStopTotal - 60; } HourStopTotal = HourStopTotal + HourStop; if (HourStopTotal >= 24) { dayStopTotal++; HourStopTotal = HourStopTotal - 24; } dayStopTotal = dayStopTotal + dayStop; } } time2 = null; if (FistTime == null) { FistTime = msgMode.CreateOn; } } else if (msgMode.Msg == "关闭" && time1 != null) { LastTime = msgMode.CreateOn; time2 = string.IsNullOrEmpty(msgMode.CreateOn) ? null : Convert.ToDateTime(msgMode.CreateOn); TimeSpan timeDiff = (time2.Value).Subtract(time1.Value); day = timeDiff.Days; Hour = timeDiff.Hours; minutes = timeDiff.Minutes; minutesTotal = minutesTotal + minutes; if (minutesTotal >= 60) { HourTotal++; minutesTotal = minutesTotal - 60; } HourTotal = HourTotal + Hour; if (HourTotal >= 24) { dayTotal++; HourTotal = HourTotal - 24; } dayTotal = dayTotal + day; } } catch (Exception ex) { } } } DevAlarmCount alarmCount = new DevAlarmCount(); alarmCount.FiratOnDate = FistTime; alarmCount.LastOffDate = LastTime; alarmCount.nowTime = day + "." + Hour + "." + minutes; alarmCount.TotalTime = dayTotal + "." + HourTotal + "." + minutesTotal; alarmCount.nowSpotTime = dayStop + "." + HourStop + "." + minutesStop; alarmCount.TotalSpotTime = dayStopTotal + "." + HourStopTotal + "." + minutesStopTotal; return Task.FromResult(alarmCount); } } }