mqtt/WebMonitor/MqttMsgServer/Service/ClientMessage/ClientMessageService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using ConsoleHttp.Model;
using MqttMsgServer.Model;
using MqttMsgServer.Service.ClientMessage.Dto;
using MqttMsgServer.Tools;
using StackExchange.Redis;

namespace MqttMsgServer.Service.ClientMessage
{
   public class ClientMessageService
    {
        public ResponseResult QueryMsgInfo(MsgQueryParamDto input)
        {
            ResponseResult result = new ResponseResult();
            string strWhere = "";
            if (!string.IsNullOrEmpty(input.ClientId) )
            {
                var cids = input.ClientId.Split(',');
                strWhere += "(";
                foreach (var cid in cids)
                {
                    strWhere += $" ClientId like '%{cid}%' or";
                }
                strWhere = strWhere.Substring(0, strWhere.LastIndexOf("or", StringComparison.Ordinal)==-1? strWhere.Length: strWhere.LastIndexOf("or", StringComparison.Ordinal));
                strWhere += ")";

            }
            if (!string.IsNullOrEmpty(input.Topic))
            {
                var ts = input.Topic.Split(',');
                strWhere += string.IsNullOrEmpty(strWhere)?"(":" and (";
                foreach (var t in ts)
                {
                    strWhere += $" Topic like '%{t}%' or ";
                }
                strWhere = strWhere.Substring(0, strWhere.LastIndexOf("or", StringComparison.Ordinal) == -1 ? strWhere.Length : strWhere.LastIndexOf("or", StringComparison.Ordinal));
                strWhere += ")";

            }

            if (input?.StartDate != null )
            {
              
                strWhere += string.IsNullOrEmpty(strWhere) ? "" : " and ";
                strWhere += $" CreatorDate >= '{input.StartDate?.ToString("yyyy-MM-dd HH:mm:ss")}' ";
            }
            if (input?.EndDate != null)
            {
                if (input?.StartDate != null && input?.EndDate <= input?.StartDate)
                {
                    result.ErrorMessage = "结束时间需要在起始时间之后！";
                    return result;
                }
                strWhere += string.IsNullOrEmpty(strWhere) ? "" : " and ";
                strWhere += $" CreatorDate <= '{input.EndDate?.ToString("yyyy-MM-dd HH:mm:ss")}' ";
            }
            string lcSql = $"select * from ReceiveMessageRecords where {strWhere}";
            DataTable dt = SqlDbHelper.ExecuteDataTable(lcSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                result.IsSuccess = true;
                result.Data = StringHelper.ToList<ReceiveMessageRecord>(dt);
            }

            return result;
        }

        public ResponseResult CountMsgInfo(MsgQueryCount input)
        {
            ResponseResult result = QueryMsgInfo(input);
            if (!result.IsSuccess)
            {
                return result;
            }
            
            if (string.IsNullOrEmpty(input.QueryCountType))
            {
                result.IsSuccess = false;
                result.ErrorMessage = "分组类型为空！";
                return result;
            }
            List<ReceiveMessageRecord> dataList = (List<ReceiveMessageRecord>)result.Data;
            if (dataList==null||!dataList.Any())
            {
                result.IsSuccess = false;
                result.ErrorMessage = "未查询到消息数据";
                return result;
            }
            string dateFormatter = "yyyy-MM";
            switch (input.QueryCountType)
            {
                case "0":
                    dateFormatter = "yyyy-MM-dd HH:mm";
                    break;
                case "1":
                    dateFormatter = "yyyy-MM-dd HH";
                    break;
                case "2":
                    dateFormatter = "yyyy-MM-dd";
                    break;
                case "3":
                    dateFormatter = "yyyy-MM";
                    break;
                case "4":
                    dateFormatter = "yyyy";
                    break;
            }
            var countEntity = dataList.OrderBy(i => i.CreatorDate).GroupBy(i => i.CreatorDate.ToString(dateFormatter))
                .Select(g => new { CreatorDate = g.Key, CountInfo = g.Count()}).ToList();
            result.IsSuccess = true;
            result.Data = countEntity;
            return result;
        }

    }
}