qlhb/Ropin.Inspection.Repository/SYS/TsysMessageRepository.cs

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);
        }
    }
}