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<TMTN_PushMsgResult, string>, ITmtnPushMsgResultRepository
{
public TmtnPushMsgResultRepository(InspectionDbContext DbContext) : base(DbContext)
{
}
public Task<bool> 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<IEnumerable<TMTN_PushMsgResultModel>> 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<TMTN_PushMsgResultModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TMTN_PushMsgResultModel>(DbContext.Database, sql.ToString(), parameters);
return Task.FromResult(recordItemlist);
}
/// <summary>
/// 消息报警天数计量
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public Task<DevAlarmCount> 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<TMTN_PushMsgResultModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TMTN_PushMsgResultModel>(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<TpushMsgModel>(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);
}
}
}