qlhb/Ropin.Inspection.Repository/TmtnPushMsgResultRepository.cs

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