using Newtonsoft.Json;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Common;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel.MTN;
using Ropin.Inspection.Model.ViewModel.MTN;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Ropin.Inspection.Repository
{
public class TsysMessageRepository : RepositoryBase<TSYS_Message, string>, ITsysMessageRepository
{
public TsysMessageRepository(InspectionDbContext dbContext) : base(dbContext)
{
}
public Task<bool> UpdateMsgStatus(string id,int? msgStatus)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("code", id),
new MySqlConnector.MySqlParameter("msgStatus", msgStatus)
};
string sql = $" UPDATE TSYS_Message SET I_MsgStatus = @msgStatus WHERE (C_ID =@code); ";
int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters);
bool result = iResult > 0;
return Task.FromResult(result);
}
public Task<IEnumerable<TsysMessageViewModel>> GetList(TsysMessageSearchModel searchModel)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
new MySqlConnector.MySqlParameter("id", searchModel.C_ID),
new MySqlConnector.MySqlParameter("generationType", searchModel.I_GenerationType),
new MySqlConnector.MySqlParameter("msgTypeCode", searchModel.C_MsgTypeCode),
new MySqlConnector.MySqlParameter("pushMsgToCode", searchModel.C_PushMsgToCode),
new MySqlConnector.MySqlParameter("storeCode", searchModel.C_StoreCode),
new MySqlConnector.MySqlParameter("devCode", searchModel.C_DevCode),
new MySqlConnector.MySqlParameter("content", "%"+searchModel.C_Content+"%"),
new MySqlConnector.MySqlParameter("beginTme", searchModel.BeginTime?.ToString("yyyy-MM-dd")),
new MySqlConnector.MySqlParameter("endTime ", searchModel.EndTime?.ToString("yyyy-MM-dd"))
};
StringBuilder sql = new StringBuilder();
StringBuilder pushMsgSql= new StringBuilder();
if (!string.IsNullOrEmpty(searchModel.C_PushMsgToCode))
{
pushMsgSql.Append(" and C_PushMsgToCode=@pushMsgToCode ");
}
sql.Append(@"
select * from (
select m.*,d.C_StoreCode,d.C_Name as C_DevName,d.C_Url,d.C_StaticUrl,c.C_Name as C_MsgTypeName,ifnull(f.cut4,0) as IsVideo
from TSYS_Message m
LEFT JOIN TDEV_DevStore d on (m.C_DevStoreCode=d.C_ID)
LEFT JOIN TBDM_CodeDetail c on (m.C_MsgTypeCode=c.C_Code)
LEFT JOIN ( select C_MessageCode,count(CASE WHEN C_Type='FILE_TYP_001' THEN 1 END) as cut1,
count(CASE WHEN C_Type='FILE_TYP_002' THEN 1 END) as cut2,count(CASE WHEN C_Type='FILE_TYP_003' THEN 1 END) as cut3,
count(CASE WHEN C_Type='FILE_TYP_004' THEN 1 END) as cut4,count(CASE WHEN C_Type='FILE_TYP_005' THEN 1 END) as cut5,
count(CASE WHEN C_Type='FILE_TYP_006' THEN 1 END) as cut6
from TSYS_MessageFile group by C_MessageCode) f on (m.C_ID=f.C_MessageCode)
) tab ");
if (!string.IsNullOrEmpty(searchModel.C_Status))
{
sql.Append(" where C_Status=@Status ");
}
else
{
sql.Append(" where C_Status!='0' ");
}
if (!string.IsNullOrEmpty(searchModel.C_ID))
{
sql.Append(" and C_ID=@id ");
}
if (searchModel.I_GenerationType!=null)
{
sql.Append(" and I_GenerationType=@generationType ");
}
if (searchModel.MsgStatus != null&& searchModel.MsgStatus.Count>0)
{
string msgStatus=string.Join(", ", searchModel.MsgStatus);
sql.Append($" and I_MsgStatus in ({msgStatus})");
}
if (!string.IsNullOrEmpty(searchModel.C_MsgTypeCode))
{
sql.Append(" and C_MsgTypeCode=@msgTypeCode ");
}
if (searchModel.MsgTypeList != null && searchModel.MsgTypeList.Count > 0)
{
StringBuilder contsql = new StringBuilder();
sql.Append($" and C_MsgTypeCode in ('{String.Join("','", searchModel.MsgTypeList)}') ");
}
if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
{
sql.Append(" and C_StoreCode=@storeCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_DevCode))
{
sql.Append(" and C_DevStoreCode=@devCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_Content))
{
sql.Append(" and C_Content like @content ");
}
if (searchModel.BeginTime != null && searchModel.BeginTime != DateTime.MinValue)
{
sql.Append(" and DATE_FORMAT(D_MsgCreateOn, '%Y-%m-%d')>=@beginTme ");
}
if (searchModel.EndTime != null && searchModel.EndTime != DateTime.MinValue)
{
sql.Append(" and DATE_FORMAT(D_MsgCreateOn, '%Y-%m-%d')<=@endTime ");
}
if (pushMsgSql.Length>0)
{
sql.Append($" and C_ID in (select C_MessageCode from TMTN_PushMsgResult where C_Status='1' {pushMsgSql.ToString()} group by C_MessageCode) ");
}
sql.Append(" order by D_MsgCreateOn desc ");
IEnumerable<TsysMessageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TsysMessageViewModel>(DbContext.Database, sql.ToString(), parameters);
searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0;
if (searchModel.TotalCount == 0)
{
recordItemlist = new List<TsysMessageViewModel>();
}
return Task.FromResult(searchModel.IsPagination ? recordItemlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist);
}
/// <summary>
/// 获取设备运行时长
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public Task<DevAlarmCount> GetMsgDevRunTimeAsync(TsysMessageSearchModel searchModel)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("devStoreCode", searchModel.C_DevCode),
new MySqlConnector.MySqlParameter("time", searchModel.BeginTime),
};
StringBuilder sql = new StringBuilder();
sql.Append($"select C_Content,D_MsgCreateOn from TSYS_Message where C_Status='1' and C_MsgTypeCode='MSG_TYPE_011' ");
if (!string.IsNullOrEmpty(searchModel.C_DevCode))
{
sql.Append(" and C_DevStoreCode=@devStoreCode");
}
if (searchModel.BeginTime != null)
{
sql.Append(" and D_MsgCreateOn> @time ");
}
sql.Append(" order by D_MsgCreateOn asc ");
IEnumerable<TSYS_Message> recordItemlist = EntityFrameworkCoreExtensions.GetList<TSYS_Message>(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)
{
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);
}
}
}