hotline/src/Hotline.Repository.SqlSugar/CallCenter/TrCallRecordRepository.cs

using Hotline.CallCenter.Calls;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.DataPermissions;
using Hotline.Settings;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.TrCallCenter;
using Hotline.Share.Enums.CallCenter;
using SqlSugar;
using XF.Domain.Dependency;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace Hotline.Repository.SqlSugar.CallCenter
{
    public class TrCallRecordRepository : BaseRepository<TrCallRecord>, ITrCallRecordRepository, IScopeDependency
    {
        public TrCallRecordRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder, IServiceProvider serviceProvider) : base(uow, dataPermissionFilterBuilder, serviceProvider)
        {
        }

        public async Task<List<BiCallDto>> GetQueryCalls(DateTime beginDate, DateTime endDate, string? Line)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();

            var list = Db.Queryable<TrCallRecord>()
                 .Where(p => p.CreatedTime >= beginDate && p.CreatedTime <= endDate && p.CallDirection == ECallDirection.In)
                 .WhereIF(!string.IsNullOrEmpty(Line), p => p.Gateway == Line)
               .GroupBy(p => p.CreatedTime.Hour)
               .Select(p => new
               {
                   Hour = p.CreatedTime.Hour, //小时段
                   Total = SqlFunc.AggregateCount(p.Id),
                   Answered = SqlFunc.AggregateSum(SqlFunc.IIF(p.AnsweredTime != null, 1, 0)), //应答数
                   Hanguped = SqlFunc.AggregateSum(SqlFunc.IIF(p.AnsweredTime == null && p.EndBy != null && p.EndBy.Value == EEndBy.To, 1, 0)),//挂断数
               })
               // .GroupBy(p => p.Hour)
               .MergeTable();

            var listCall = await listHour.LeftJoin(list, (x, p) => x.ColumnName == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new BiCallDto()
               {
                   Hour = x.ColumnName,
                   Total = p.Total,
                   Answered = p.Answered,
                   Hanguped = p.Hanguped,
               })
               .ToListAsync();
            foreach (var call in listCall)
            {
                call.HourRange = call.Hour.ToString().PadLeft(2, '0') + ":00 - " + (call.Hour + 1).ToString().PadLeft(2, '0') + ":00";
            }

            return listCall;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="noConnectByeTimes"></param>
        /// <param name="effectiveTimes"></param>
        /// <param name="connectByeTimes"></param>
        /// <param name="CallInOverConnRingTime"></param>
        /// <param name="SeatChaoTime"></param>
        /// <param name="Line"></param>
        /// <returns></returns>
        public async Task<List<QueryCallsDetailDto>> QueryCallsHourDetail(DateTime beginDate, DateTime endDate, int noConnectByeTimes, int effectiveTimes, int connectByeTimes, int CallInOverConnRingTime, int SeatChaoTime, string? Line)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();

            var list = Db.Queryable<TrCallRecord>()
                  .Where(p => p.CreatedTime >= beginDate && p.CreatedTime <= endDate)
                 // .Where(p => p.Gateway != "82826886" && SqlFunc.Length(p.Gateway) != 4)
                  .WhereIF(!string.IsNullOrEmpty(Line), p => p.Gateway == Line)
                   .GroupBy(p => p.CreatedTime.Hour)
                   .Select(p => new
                   {
                       Hour = p.CreatedTime.Hour, //小时段
                       InTotal = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In, 1, 0)),//呼入总量
                       InConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState == EOnState.On && p.CallDirection == ECallDirection.In && p.AnsweredTime != null, 1, 0)),//呼入接通量
                       NotAcceptedHang = SqlFunc.AggregateSum(SqlFunc.IIF(p.Duration == 0 && p.RingTimes <= noConnectByeTimes && p.RingTimes > 0, 1, 0)), //未接通秒挂
                       TotalDurationIncomingCalls = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In && p.AnsweredTime != null && p.OnState == EOnState.On, p.Duration, 0)), //呼入总时长
                       InAvailableAnswer = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In && p.AnsweredTime != null && p.Duration >= effectiveTimes, 1, 0)),//有效接通量
                       InHangupImmediateWhenAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In && p.Duration > 0 && p.Duration <= connectByeTimes, 1, 0)), //呼入接通秒挂
                       TimeoutConnection = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState == EOnState.On && p.CallDirection == ECallDirection.In && p.AnsweredTime != null && p.RingTimes >= CallInOverConnRingTime, 1, 0)),//超时接通量
                       TimeoutSuspension = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState == EOnState.On && p.CallDirection == ECallDirection.In && p.AnsweredTime != null && p.Duration >= SeatChaoTime, 1, 0)),//超时挂断量
                       QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In && p.QueueTims > 0 && p.RingTimes == 0 && p.OnState == EOnState.NoOn, 1, 0)), //队列挂断
                       IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.In && p.BeginIvrTime.HasValue && !p.BeginQueueTime.HasValue && !p.BeginRingTime.HasValue && p.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
                       OutTotal = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallDirection == ECallDirection.Out, 1, 0)),//呼出总量
                       OutConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState == EOnState.On && p.CallDirection == ECallDirection.Out && p.AnsweredTime != null, 1, 0))
                   })
                   .MergeTable();

            var listCall = await listHour.LeftJoin(list, (x, p) => x.ColumnName == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new QueryCallsDetailDto()
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   InTotal = p.InTotal,
                   InConnectionQuantity = p.InConnectionQuantity,
                   NotAcceptedHang = p.NotAcceptedHang,
                   TotalDurationIncomingCalls = p.TotalDurationIncomingCalls,
                   InAvailableAnswer = p.InAvailableAnswer,
                   InHangupImmediateWhenAnswered = p.InHangupImmediateWhenAnswered,
                   TimeoutConnection = p.TimeoutConnection,
                   TimeoutSuspension = p.TimeoutSuspension,
                   QueueByeCount = p.QueueByeCount,
                   IvrByeCount = p.IvrByeCount,
                   OutTotal = p.OutTotal,
                   OutConnectionQuantity = p.OutConnectionQuantity
               })
               .ToListAsync();

            return listCall;
        }

        public async Task<List<TrCallHourDto>?> GetCallHourList(DateTime beginDate, DateTime? endDate, int noConnectByeTimes, int effectiveTimes, int connectByeTimes, string source)
        {
            TimeSpan timeDifference = endDate.Value.Subtract(beginDate).Duration();

            int hourDiff = (int)(timeDifference.TotalHours);

            //计算时间差
            int hour = Convert.ToInt32((endDate - beginDate).Value.TotalHours);

            List<DateTime> dts = new List<DateTime>() { beginDate };
            for (int i = 0; i < hour - 1; i++)
            {
                dts.Add(dts.Last().AddHours(1));
            }

            var list = await Db.Reportable(dts).ToQueryable<DateTime>()
                .LeftJoin<TrCallRecord>((it, o) => o.CreatedTime >= it.ColumnName && o.CreatedTime < it.ColumnName.AddHours(1) && o.CallDirection == ECallDirection.In)
                 //.Where((it, o) => o.CallDirection == ECallDirection.In)
                 .WhereIF(!string.IsNullOrEmpty(source), (it, o) => o.CDPN == source)
                .GroupBy((it, o) => it.ColumnName)
                .Select((it, o) => new TrCallHourDto()
                {
                    DateTimeTo = it.ColumnName,
                    Hour = it.ColumnName.Hour, //小时段
                    EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.Duration >= effectiveTimes, 1, 0)),//有效接通
                    ConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.Duration > 0 && o.Duration <= connectByeTimes, 1, 0)), //接通秒挂
                    NoConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.Duration == 0 && o.RingTimes <= noConnectByeTimes && o.RingTimes > 0, 1, 0)), //未接通秒挂
                    QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.QueueTims > 0 && o.RingTimes == 0 && o.OnState == EOnState.NoOn, 1, 0)), //队列挂断
                    IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.BeginIvrTime.HasValue && !o.BeginQueueTime.HasValue && !o.BeginRingTime.HasValue && o.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
                })
                .MergeTable()
                .OrderBy(x => x.Hour)
                .ToListAsync();

            var resultList = list.GroupBy(x => x.Hour)
                 .Select(x => new TrCallHourDto()
                 {
                     Hour = x.Key,
                     HourTo = x.Key.ToString().PadLeft(2, '0') + ":00 - " + (x.Key).ToString().PadLeft(2, '0') + ":59",
                     StartHourTo = x.Key.ToString().PadLeft(2, '0') + ":00",
                     EndHourTo = x.Key.ToString().PadLeft(2, '0') + ":59",
                     EffectiveCount = x.Sum(d => d.EffectiveCount),
                     ConnectByeCount = x.Sum(d => d.ConnectByeCount),
                     NoConnectByeCount = x.Sum(d => d.NoConnectByeCount),
                     QueueByeCount = x.Sum(d => d.QueueByeCount),
                     IvrByeCount = x.Sum(d => d.IvrByeCount),
                 }).OrderBy(x => hour).ToList();

            return resultList;
        }

		/// <summary>
		/// 通话时段统计明细
		/// </summary>
		/// <returns></returns>
		public async Task<TotalData<BiSeatSwitchDto>> GetCallList(QueryCallListDto dto, int noConnectByeTimes, int effectiveTimes, int connectByeTimes)
		{
			TimeSpan endHourTo = DateTime.Now.TimeOfDay;
            if (dto.StartHourTo.HasValue)
            {
                endHourTo = dto.StartHourTo.Value.Add(new TimeSpan(1, 0, 0));
            }
            RefAsync<int> total = 0;
            var res = await Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= dto.StartTime && x.CreatedTime <= dto.EndTime)
                .Where(x => x.CallDirection == ECallDirection.In)
                .WhereIF(!string.IsNullOrEmpty(dto.Source), x => x.CDPN == dto.Source)
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && ("QueueBye".Equals(dto.Type) || "queueByeCount".Equals(dto.Type)), x => x.QueueTims > 0 && x.RingTimes == 0) //队列挂断
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && ("IvrBye".Equals(dto.Type) || "ivrByeCount".Equals(dto.Type)), x => x.BeginIvrTime.HasValue && !x.BeginQueueTime.HasValue && !x.BeginRingTime.HasValue && x.OnState == EOnState.NoOn)//IVR挂断
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && ("Effective".Equals(dto.Type) || "effectiveCount".Equals(dto.Type)), x => x.Duration >= effectiveTimes) //有效接通
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && "Invalid".Equals(dto.Type), x => x.Duration > 0 && x.Duration < effectiveTimes)//无效接通
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && "connectByeCount".Equals(dto.Type), x => x.Duration > 0 && x.Duration <= connectByeTimes)  //接通秒挂
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && "noConnectByeCount".Equals(dto.Type), x => x.Duration == 0 && x.RingTimes <= noConnectByeTimes && x.RingTimes > 0)  //未接通秒挂
                .WhereIF(!string.IsNullOrEmpty(dto.Type) && "count".Equals(dto.Type), x =>
                (x.Duration == 0 && x.RingTimes <= noConnectByeTimes && x.RingTimes > 0)  //未接通秒挂
                || (x.Duration > 0 && x.Duration <= connectByeTimes)  //接通秒挂
                || (x.Duration >= effectiveTimes)//有效接通
                || (x.BeginIvrTime.HasValue && !x.BeginQueueTime.HasValue && !x.BeginRingTime.HasValue && x.OnState == EOnState.NoOn) //IVR挂断
                || (x.QueueTims > 0 && x.RingTimes == 0))//队列挂断
                .WhereIF(dto.StartHourTo.HasValue, x => SqlFunc.ToTime(x.CreatedTime.ToString("HH:mm:ss")) >= dto.StartHourTo.Value && SqlFunc.ToTime(x.CreatedTime.ToString("HH:mm:ss")) < endHourTo)
                .Select(x => new BiSeatSwitchDto
                {
                    CPN = x.CPN,
                    CDPN = x.CDPN,
                    CreatedTime = x.CreatedTime
                })
				.OrderByIF(dto is { SortRule: 0, SortField: "createdTime" }, x => x.CreatedTime, OrderByType.Asc)
		        .OrderByIF(dto is { SortRule: 1, SortField: "createdTime" }, x => x.CreatedTime, OrderByType.Desc)
				.ToPageListAsync(dto.PageIndex, dto.PageSize, total);
            return new TotalData<BiSeatSwitchDto>(res, total.Value);
        }

        public async Task<List<CallHotLineDto>> GetCallHotLineList(DateTime beginDate, DateTime endDate, string lineNum, int noConnectByeTimes, int effectiveTimes, int connectByeTimes, int ringTims)
        {
            var list = await Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= beginDate && x.CreatedTime <= endDate && SqlFunc.Length(x.Gateway)>4)
                .WhereIF(!string.IsNullOrEmpty(lineNum), x => x.Gateway == lineNum)
                .GroupBy(x => x.Gateway)
                .Select(x => new CallHotLineDto()
                {
                    GateWay = x.Gateway,
                    CallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In, 1, 0)),//接通
                    ConnectCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.OnState == EOnState.On, 1, 0)),//接通
                    NoConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.Duration == 0 && x.RingTimes <= noConnectByeTimes && x.RingTimes > 0, 1, 0)), //未接通秒挂
                    EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.Duration >= effectiveTimes, 1, 0)),//有效接通
                    DurationSum = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.OnState == EOnState.On, x.Duration, 0)),//通话总时长
                    ConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.Duration > 0 && x.Duration <= connectByeTimes, 1, 0)), //接通秒挂
                    TimelyAnswerCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.CallDirection == ECallDirection.In && x.OnState == EOnState.On && x.RingTimes <= ringTims, 1, 0))//及时应答
                }).ToListAsync();

            return list;
        }


        public ISugarQueryable<TrCallRecord> GetCallList(GetCallListDto dto)
        {
           return  Db.Queryable<TrCallRecord>()
                .Includes(x => x.Order)
                .WhereIF(!string.IsNullOrEmpty(dto.CPN), x => x.CPN.Contains(dto.CPN))
                .WhereIF(!string.IsNullOrEmpty(dto.CDPN), x => x.CDPN.Contains(dto.CDPN))
                .WhereIF(!string.IsNullOrEmpty(dto.TelNo), x => x.TelNo.Contains(dto.TelNo))
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), x => x.UserName.Contains(dto.UserName))
                .WhereIF(dto.CallDirection != null, x => x.CallDirection == dto.CallDirection)
                .WhereIF(dto.OnState != null, x => x.OnState == dto.OnState)
                .WhereIF(dto.EndBy != null, x => x.EndBy == dto.EndBy)

                .WhereIF(dto.BeginIvrTimeStart.HasValue, x => x.BeginIvrTime >= dto.BeginIvrTimeStart)
                .WhereIF(dto.BeginIvrTimeEnd.HasValue, x => x.BeginIvrTime <= dto.BeginIvrTimeEnd)

                .WhereIF(dto.EndIvrTimeStart.HasValue, x => x.EndIvrTime >= dto.EndIvrTimeStart)
                .WhereIF(dto.EndIvrTimeEnd.HasValue, x => x.EndIvrTime <= dto.EndIvrTimeEnd)

                .WhereIF(dto.BeginQueueTimeStart.HasValue, x => x.BeginQueueTime >= dto.BeginQueueTimeEnd)
                .WhereIF(dto.BeginQueueTimeEnd.HasValue, x => x.BeginQueueTime <= dto.BeginQueueTimeEnd)

                .WhereIF(dto.EndQueueTimeStart.HasValue, x => x.EndQueueTime >= dto.EndQueueTimeStart)
                .WhereIF(dto.EndQueueTimeEnd.HasValue, x => x.EndQueueTime <= dto.EndQueueTimeEnd)

                .WhereIF(dto.AnsweredTimeStart.HasValue, x => x.AnsweredTime >= dto.AnsweredTimeStart)
                .WhereIF(dto.AnsweredTimeEnd.HasValue, x => x.AnsweredTime <= dto.AnsweredTimeEnd)

                .WhereIF(dto.OverTimeStart.HasValue, x => x.OverTime >= dto.OverTimeStart)
                .WhereIF(dto.OverTimeEnd.HasValue, x => x.OverTime <= dto.OverTimeEnd)

                .WhereIF(dto.BeginRingTimeStart.HasValue, x => x.BeginRingTime >= dto.BeginRingTimeStart)
                .WhereIF(dto.BeginRingTimeEnd.HasValue, x => x.BeginRingTime <= dto.BeginRingTimeEnd)

                .WhereIF(dto.EndRingTimeStart.HasValue, x => x.EndRingTimg >= dto.EndRingTimeStart)
                .WhereIF(dto.EndRingTimeEnd.HasValue, x => x.EndRingTimg <= dto.EndRingTimeEnd)

                .WhereIF(dto.CallTimeStart.HasValue, x => x.CreatedTime >= dto.CallTimeStart)
                .WhereIF(dto.CallTimeEnd.HasValue, x => x.CreatedTime <= dto.CallTimeEnd)

                .WhereIF(!string.IsNullOrEmpty(dto.OrderNo), x => x.Order.No.Contains(dto.OrderNo))
                .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Order.Title.Contains(dto.Title))
                .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.Gateway.Contains(dto.Gateway))
                .WhereIF(dto.IsSensitiveWord.HasValue && dto.IsSensitiveWord == true, d => d.Sensitive != null && SqlFunc.JsonArrayLength(d.Sensitive) > 0)
                .WhereIF(!string.IsNullOrEmpty(dto.SensitiveWord), d => SqlFunc.JsonArrayAny(d.Sensitive, dto.SensitiveWord))
                .WhereIF(dto.IsAiAnswered == true, x => string.IsNullOrEmpty(x.UserId) == true && x.BeginIvrTime.HasValue && x.EndIvrTime.HasValue)

                .WhereIF(dto.PhoneTypes != null, x => x.PhoneTypes == dto.PhoneTypes)
                .OrderByDescending(x => x.CreatedTime);
        }

        
        public async Task<List<QueryCallDateStatisticsDetailResp>> QueryCallDateStatisticsDetail(DateTime startTime, DateTime endTime)
        {
            var query = Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= startTime && x.CreatedTime <= endTime && SqlFunc.Length(x.Gateway) > 4 && x.CallDirection == ECallDirection.In)
                .GroupBy(x => x.CreatedTime.ToString("yyyy-MM-dd"))
                .Select(x => new QueryCallDateStatisticsDetailResp()
                {
                    Date = x.CreatedTime.ToString("yyyy-MM-dd"),
                    IvrCallInTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.BeginIvrTime.HasValue && !x.BeginQueueTime.HasValue && !x.BeginRingTime.HasValue && x.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
                    PersonCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1", 1, 0)),
                    EnterpriseCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2", 1, 0)),
                    GaoXiaoCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "3", 1, 0)),
                    AiCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Gateway == "82826886", 1, 0)),
                    PersonCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1" && x.OnState == EOnState.On, 1, 0)),
                    EnterpriseCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.OnState == EOnState.On, 1, 0)),
                    GaoXiaoCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "3" && x.OnState == EOnState.On, 1, 0)),
                    AiCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Gateway == "82826886", 1, 0)),
                    PersonRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1" && x.QueueTims > 0 && x.OnState == EOnState.NoOn, 1, 0)),//个人服务挂断
                    EnterpriseRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.QueueTims > 0 && x.OnState == EOnState.NoOn, 1, 0)), //企业挂断
                    GaoXiaoRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "3" && x.QueueTims > 0 && x.OnState == EOnState.NoOn, 1, 0)), //高效办成一件事挂断
                    IvrRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.BeginIvrTime.HasValue && !x.BeginQueueTime.HasValue && !x.BeginRingTime.HasValue && x.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
                })
                .OrderBy(x => x.Date);
                return await query.ToListAsync();
        }

        public async Task<List<QueryPersonCallDateStatisticsDetailResp>> QueryPersonCallDateStatisticsDetail(DateTime startTime, DateTime endTime)
        {
            var query = Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= startTime && x.CreatedTime <= endTime && SqlFunc.Length(x.Gateway) > 4 && x.CallDirection == ECallDirection.In)
                .GroupBy(x => x.CreatedTime.ToString("yyyy-MM-dd"))
                .Select(x => new QueryPersonCallDateStatisticsDetailResp()
                {
                    Date = x.CreatedTime.ToString("yyyy-MM-dd"),
                    PersonCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1", 1, 0)),
                    PersonCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1" && x.OnState == EOnState.On, 1, 0)),
                    //PersonRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1" && x.OnState == EOnState.NoOn, 1, 0)),//个人服务挂断
                    PersonQueueOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "1" && x.QueueTims > 0 && x.RingTimes == 0 && x.OnState == EOnState.NoOn, 1, 0)),//个人服务队列挂断
                    PersonWaitOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length -1,1) =="1" && x.RingTimes>0 && x.OnState == EOnState.NoOn,1,0)) //个人服务等待挂断
                })
                .OrderBy(x => x.Date);
            return await query.ToListAsync();
        }

        public async Task<List<QueryEnterpriseCallDateStatisticsDetailResp>> QueryEnterpriseCallDateStatisticsDetail(DateTime startTime,DateTime endTime)
        {
            var query = Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= startTime && x.CreatedTime <= endTime && SqlFunc.Length(x.Gateway) > 4 && x.CallDirection == ECallDirection.In)
                .GroupBy(x => x.CreatedTime.ToString("yyyy-MM-dd"))
                .Select(x => new QueryEnterpriseCallDateStatisticsDetailResp()
                {
                    Date = x.CreatedTime.ToString("yyyy-MM-dd"),
                    EnterpriseCallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2", 1, 0)),
                    EnterpriseCallInPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.OnState == EOnState.On, 1, 0)),
                    //EnterpriseRingOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.QueueTims > 0 && x.RingTimes == 0 && x.OnState == EOnState.NoOn, 1, 0)), //企业挂断
                    EnterpriseQueueOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.QueueTims > 0 && x.RingTimes == 0 && x.OnState == EOnState.NoOn, 1, 0)),//企业服务队列挂断
                    EnterpriseWaitOffCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.IvrDtmf.Substring(x.IvrDtmf.Length - 1, 1) == "2" && x.RingTimes > 0 && x.OnState == EOnState.NoOn, 1, 0)) //企业服务等待挂断
                })
                .OrderBy(x => x.Date);
            return await query.ToListAsync();
        }

        public async Task<List<QueryCallOutDateStatisticsDetailResp>> QueryCallOutDateStatisticsDetail(DateTime startTime,DateTime endTime,List<string> enterpriseTels)
        {
            var callTable = Db.Queryable<TrCallRecord>()
                .Where(x => x.CreatedTime >= startTime && x.CreatedTime <= endTime && SqlFunc.Length(x.Gateway) > 4 && x.CallDirection == ECallDirection.Out && SqlFunc.Length(x.TelNo)==4)
                .GroupBy(x => x.CreatedTime.ToString("yyyy-MM-dd"))
                .Select(x => new QueryCallOutDateStatisticsDetailResp()
                {
                    Date = x.CreatedTime.ToString("yyyy-MM-dd"),
                    PersonCallOutCount = SqlFunc.AggregateSum(SqlFunc.IIF(1 == 1 && !enterpriseTels.Contains(x.TelNo),1,0)),
                    EnterpriseCallOutCount = SqlFunc.AggregateSum(SqlFunc.IIF(1== 1 && enterpriseTels.Contains(x.TelNo),1,0)),
                    PersonCallOutPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(!enterpriseTels.Contains(x.TelNo) && x.OnState == EOnState.On, 1, 0)),
                    EnterpriseCallOutPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(enterpriseTels.Contains(x.TelNo) && x.OnState == EOnState.On, 1, 0)),
                }).MergeTable();

            var aiVisitCallTable =  Db.Queryable<AiOrderVisitDetail>()
                .Where(x => x.CallTime >= startTime && x.CallTime <= endTime)
                .GroupBy(x => x.CallTime.Value.ToString("yyyy-MM-dd"))
                .Select(x => new QueryCallOutDateStatisticsDetailResp()
                {
                    Date = x.CallTime.Value.ToString("yyyy-MM-dd"),
                    AiVisitCallOutCount = SqlFunc.AggregateCount(x.Id),
                    AiVisitCallOutPutthroughCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.AiIsContact!=null || x.AiVolved!=null || x.AiOrgProcessingResults != null ,1,0))
                }).MergeTable();

            var list = await callTable.LeftJoin(aiVisitCallTable, (a, b) => a.Date == b.Date)
                .Select((a, b) => new QueryCallOutDateStatisticsDetailResp()
                {
                    Date = a.Date,
                    PersonCallOutCount = a.PersonCallOutCount,
                    EnterpriseCallOutCount = a.EnterpriseCallOutCount,
                    AiVisitCallOutCount = b.AiVisitCallOutCount,
                    AiCallOutCount = 0,
                    PersonCallOutPutthroughCount= a.PersonCallOutPutthroughCount,
                    EnterpriseCallOutPutthroughCount = a.EnterpriseCallOutPutthroughCount,
                    AiVisitCallOutPutthroughCount = b.AiVisitCallOutPutthroughCount,
                    AiCallOutPutthroughCount = 0,
                }).OrderBy(a=>a.Date).ToListAsync();
            return list;
        }

    }
}