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

using Hotline.CallCenter.Calls;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.DataPermissions;
using Hotline.Settings;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.Order;
using Hotline.Share.Tools;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XF.Domain.Dependency;
using XF.Domain.Repository;

namespace Hotline.Repository.SqlSugar.CallCenter;
public class CallNativeRepository : BaseRepository<CallNative>, ICallNativeRepository, IScopeDependency
{
    public CallNativeRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
    {
    }
    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<CallNative>((it, o) => o.BeginIvrTime >= it.ColumnName && o.BeginIvrTime < it.ColumnName.AddHours(1) && o.Direction == ECallDirection.In && o.CallState != ECallState.Invalid)
              //.Where((it, o) => o.CallDirection == ECallDirection.In)
              .WhereIF(!string.IsNullOrEmpty(source), (it, o) => o.ToNo == source)
             .GroupBy((it, o) => it.ColumnName)
             .Select((it, o) => new TrCallHourDto()
             {
                 DateTimeTo = it.ColumnName,
                 Hour = it.ColumnName.Hour, //小时段
                 EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.AnsweredTime != null && o.Duration >= effectiveTimes, 1, 0)),//有效接通
                 ConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.AnsweredTime != null && o.Duration <= connectByeTimes, 1, 0)), //接通秒挂
                 NoConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.AnsweredTime == null && o.RingDuration <= noConnectByeTimes && o.RingDuration > 0, 1, 0)), //未接通秒挂
                 QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallState == ECallState.NotAcceptedHang, 1, 0)), //队列挂断
                 IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallState == ECallState.IVRNoAccept, 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;
    }


    public async Task<int> UpdateReplyTxtAsync(string callId, string replyTxt)
    {
        return await Updateable()
            .Where(m => m.Id == callId)
            .SetColumns(m => m.ReplyTxt, replyTxt)
            .ExecuteCommandAsync();
    }

    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<CallNative>()
              .Where(p => p.BeginIvrTime >= beginDate && p.BeginIvrTime <= endDate && p.CallState != ECallState.Invalid)
              .WhereIF(!string.IsNullOrEmpty(Line), p => p.TelNo == Line)
               .GroupBy(p => p.BeginIvrTime.Value.Hour)
               .Select(p => new
               {
                   Hour = p.BeginIvrTime.Value.Hour, //小时段
                   InTotal = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In, 1, 0)),//呼入总量
                   InConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.AnsweredTime != null, 1, 0)),//呼入接通量
                   NotAcceptedHang = SqlFunc.AggregateSum(SqlFunc.IIF(p.RingDuration <= noConnectByeTimes && p.RingDuration > 0 && p.Direction == ECallDirection.In, 1, 0)), //未接通秒挂
                   TotalDurationIncomingCalls = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.AnsweredTime != null, p.Duration, 0)), //呼入总时长
                   InAvailableAnswer = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.AnsweredTime != null && p.Duration >= effectiveTimes, 1, 0)),//有效接通量
                   InHangupImmediateWhenAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.Duration > 0 && p.Duration <= connectByeTimes, 1, 0)), //呼入接通秒挂
                   TimeoutConnection = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.AnsweredTime != null && p.RingDuration >= CallInOverConnRingTime, 1, 0)),//超时接通量
                   TimeoutSuspension = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.AnsweredTime != null && p.Duration >= SeatChaoTime, 1, 0)),//超时挂断量
                   QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.CallState == ECallState.NotAcceptedHang, 1, 0)), //队列挂断
                   IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.CallState == ECallState.IVRNoAccept, 1, 0)), //IVR挂断
                   OutTotal = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.Out, 1, 0)),//呼出总量
                   OutConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == 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;
    }

    /// <summary>
    /// 话务统计分析
    /// </summary>
    /// <param name="beginDate"></param>
    /// <param name="endDate"></param>
    /// <param name="Line"></param>
    /// <returns></returns>
    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<CallNative>()
             .Where(p => p.BeginIvrTime >= beginDate && p.BeginIvrTime <= endDate && p.Direction == ECallDirection.In && p.CallState != ECallState.Invalid)
             .WhereIF(!string.IsNullOrEmpty(Line), p => p.ToNo == Line)
           .GroupBy(p => p.BeginIvrTime.Value.Hour)
           .Select(p => new
           {
               Hour = p.BeginIvrTime.Value.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.CallState == ECallState.Missed || p.CallState == ECallState.NotAcceptedHang || p.CallState == ECallState.IVRNoAccept), 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>
    /// <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<CallNative>()
            .Where(x => x.BeginIvrTime >= dto.StartTime && x.BeginIvrTime <= dto.EndTime)
            .Where(x => x.Direction == ECallDirection.In && x.CallState != ECallState.Invalid)
            .WhereIF(!string.IsNullOrEmpty(dto.Source), x => x.ToNo == dto.Source)
.WhereIF(!string.IsNullOrEmpty(dto.Type) && ("QueueBye".Equals(dto.Type) || "queueByeCount".Equals(dto.Type)), x => x.CallState == ECallState.NotAcceptedHang) //队列挂断
.WhereIF(!string.IsNullOrEmpty(dto.Type) && ("IvrBye".Equals(dto.Type) || "ivrByeCount".Equals(dto.Type)), x => x.CallState == ECallState.IVRNoAccept)//IVR挂断
.WhereIF(!string.IsNullOrEmpty(dto.Type) && ("Effective".Equals(dto.Type) || "effectiveCount".Equals(dto.Type)), x => x.AnsweredTime != null && x.Duration >= effectiveTimes) //有效接通
.WhereIF(!string.IsNullOrEmpty(dto.Type) && "Invalid".Equals(dto.Type), x => x.AnsweredTime != null && x.Duration < effectiveTimes)//无效接通
            .WhereIF(!string.IsNullOrEmpty(dto.Type) && "connectByeCount".Equals(dto.Type), x => x.AnsweredTime != null && x.Duration <= connectByeTimes)  //接通秒挂
.WhereIF(!string.IsNullOrEmpty(dto.Type) && "noConnectByeCount".Equals(dto.Type), x => x.AnsweredTime == null && x.RingDuration <= noConnectByeTimes && x.RingDuration > 0)  //未接通秒挂
            .WhereIF(!string.IsNullOrEmpty(dto.Type) && "count".Equals(dto.Type), x =>
            (x.AnsweredTime == null && x.RingDuration <= noConnectByeTimes && x.RingDuration > 0)  //未接通秒挂
            || (x.AnsweredTime != null && x.Duration <= connectByeTimes)  //接通秒挂
            || (x.AnsweredTime != null && x.Duration >= effectiveTimes)//有效接通
            || (x.CallState == ECallState.IVRNoAccept) //IVR挂断
            || (x.CallState == ECallState.NotAcceptedHang))//队列挂断
            .WhereIF(dto.StartHourTo.HasValue, x => SqlFunc.ToTime(x.BeginIvrTime.Value.ToString("HH:mm:ss")) >= dto.StartHourTo.Value && SqlFunc.ToTime(x.BeginIvrTime.Value.ToString("HH:mm:ss")) < endHourTo)
            .Select(x => new BiSeatSwitchDto
            {
                Id = x.Id,
                CPN = x.FromNo,
                CDPN = x.ToNo,
                CreatedTime = x.BeginIvrTime.Value
            })
            .ToPageListAsync(dto.PageIndex, dto.PageSize, total);
        return new TotalData<BiSeatSwitchDto>(res, total.Value);
    }

    public async Task<List<CallHotLineDto>> GetCallHotLineListAsync(BiQueryGateWayDto dto, int noConnectByeTimes, int effectiveTimes, int connectByeTimes, int ringTims, List<string> hotLines)
    {
        var list = await Db.Queryable<CallNative>()
            .Where(x => x.BeginIvrTime >= dto.StartTime && x.BeginIvrTime <= dto.EndTime && SqlFunc.Length(x.ToNo) > 4 && x.CallState != ECallState.Invalid)
            .Where(x => hotLines.Contains(x.ToNo))
            .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.ToNo == dto.Gateway)
            .GroupBy(x => x.ToNo)
            .Select(x => new CallHotLineDto()
            {
                GateWay = x.ToNo,
                CallInCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In, 1, 0)),//呼入
                ConnectCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.AnsweredTime != null, 1, 0)),//接通
                NoConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.Duration == 0 && x.RingDuration <= noConnectByeTimes && x.RingDuration > 0, 1, 0)), //未接通秒挂
                EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.Duration >= effectiveTimes, 1, 0)),//有效接通
                DurationSum = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.AnsweredTime != null, x.Duration, 0)),//通话总时长
                ConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.Duration > 0 && x.Duration <= connectByeTimes, 1, 0)), //接通秒挂
                TimelyAnswerCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Direction == ECallDirection.In && x.AnsweredTime != null && x.RingDuration <= ringTims, 1, 0))//及时应答
            }).ToListAsync();

        return list;
    }
}