hotline/src/Hotline.Application/StatisticalReport/CallReport/CallReportApplicationBase.cs

using Hotline.Caching.Interfaces;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.CallCenter.Calls;
using Hotline.Repository.SqlSugar.CallCenter;
using Hotline.Settings;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.TrCallCenter;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Requests;
using SqlSugar;
using XF.Domain.Exceptions;
using Hotline.Orders;
using Hotline.Share.Enums.User;
using Hotline.Users;
using XF.Domain.Repository;
using Hotline.CallCenter.Tels;
using Hotline.Share.Dtos;
using Hotline.Share.Tools;
using JiebaNet.Segmenter.Common;

namespace Hotline.Application.StatisticalReport.CallReport;

public abstract class CallReportApplicationBase : ICallReportApplication
{
    private readonly ISystemSettingCacheManager _systemSettingCacheManager;
    private readonly ICallNativeRepository _callNativeRepository;
    private readonly IRepository<User> _userRepository;
    private readonly IRepository<Work> _workRepository;
    private readonly IRepository<TelRest> _telRestRepository;

    protected CallReportApplicationBase(ISystemSettingCacheManager systemSettingCacheManager, ICallNativeRepository callNativeRepository, IRepository<User> userRepository, IRepository<Work> workRepository, IRepository<TelRest> telRestRepository)
    {
        _systemSettingCacheManager = systemSettingCacheManager;
        _callNativeRepository = callNativeRepository;
        _userRepository = userRepository;
        _workRepository = workRepository;
        _telRestRepository = telRestRepository;
    }

    public virtual async Task<List<CallHotLineDto>> GetCallHotLineListAsync(BiQueryGateWayDto dto, CancellationToken requestAborted)
    {
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;
        int ringTimes = _systemSettingCacheManager.RingTimes;
        return await _callNativeRepository.GetCallHotLineListAsync(dto, noConnectByeTimes, effectiveTimes, connectByeTimes, ringTimes);
    }

    public virtual async Task<List<TrCallHourDto>> GetCallHourListAsync(BiQueryHourCallDto dto, CancellationToken requestAborted)
    {
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;
        return await _callNativeRepository.GetCallHourList(dto.StartTime, dto.EndTime, noConnectByeTimes, effectiveTimes, connectByeTimes, dto.Source);
    }

    public virtual async Task<TotalData<BiSeatSwitchDto>> GetCallListAsync(QueryCallListDto dto, CancellationToken requestAborted)
    {
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;
        return await _callNativeRepository.GetCallList(dto, noConnectByeTimes, effectiveTimes, connectByeTimes);
    }

    public virtual async Task<List<BiCallDto>> QueryCallsAsync(BiQueryCallsDto dto, CancellationToken cancellationToken)
    {
        return await _callNativeRepository.GetQueryCalls(dto.StartTime.Value, dto.EndTime.Value, dto.Line);
    }

    /// <summary>
    /// 话务日期明细
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public virtual async Task<List<QueryCallsDetailDto>> QueryCallsDetailAsync(BiQueryCallsDto dto)
    {
        //超时接通量
        int CallInOverConnRingTime = _systemSettingCacheManager.CallInOverConnRingTime;
        //坐席超时挂断时间
        int SeatChaoTime = _systemSettingCacheManager.SeatChaoTime;
        //未接秒挂时间
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        //呼入有效时间
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        //接通秒挂时间
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;

        var callData = await _callNativeRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 // .Where(p => p.Gateway != "82826886" && SqlFunc.Length(p.Gateway) != 4)
                 .WhereIF(!string.IsNullOrEmpty(dto.Keyword), p => p.ToNo == dto.Keyword)
                 .GroupBy(p => p.CreationTime.ToString("yyyy-MM-dd"))
                .Select(p => new QueryCallsDetailDto
                {
                    Date = p.CreationTime.ToString("yyyy-MM-dd"),
                    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.Duration == 0 && 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.WaitDuration > 0 && p.RingDuration == 0 && p.AnsweredTime == null, 1, 0)), //队列挂断
                    IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.BeginIvrTime.HasValue && !p.BeginQueueTime.HasValue && !p.BeginRingTime.HasValue && p.AnsweredTime == null, 1, 0)), //IVR挂断
                    OutTotal = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.Out, 1, 0)),//呼出总量
                    OutConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.AnsweredTime != null && p.Direction == ECallDirection.Out && p.AnsweredTime != null, 1, 0))
                })
                .OrderBy(p => p.Date)
                .ToListAsync();

        return callData;
    }

    /// <summary>
    /// 话务日期明细-呼入总量/接通总量
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public virtual async Task<(int, List<CallRecordOutDto>)> QueryCallsDetailInTotalAsync(BiQueryCallsDto dto, bool isAll)
    {
        var recordPrefix = _systemSettingCacheManager.RecordPrefix;
        var query = _callNativeRepository.Queryable()
                .LeftJoin<Order>((p, o) => p.Id == o.CallId)
                .Where((p, o) => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime && p.Direction == ECallDirection.In)
                .WhereIF(dto.TypeCode == "2", (p, o) => p.AnsweredTime != null)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), (p, o) => p.TelNo == dto.Keyword)
                 .OrderByDescending((p, o) => p.CreationTime)
                 .Select((p, o) => new CallRecordOutDto
                 {
                     OtherAccept = p.Id,
                     OrderId = o.Id,
                     OrderNo = o.No,
                     OrderTitle = o.Title,
                     Cdpn = p.ToNo,
                     Cpn = p.FromNo,
                     RecordingFileUrl = recordPrefix + p.AudioFile,
                     RecordingFileName = p.AudioFile,
                     RecordingBaseAddress = recordPrefix,
                     RecordingAbsolutePath = p.AudioFile
                 }, true);

        if (isAll)
        {
            return (0, await query.ToListAsync());
        }
        return await query.ToPagedListAsync(dto.PageIndex, dto.PageSize);
    }

    public virtual async Task<List<QueryCallsDetailStatistics>> QueryCallsDetailStatisticsAsync(StartEndTimeDto dto, CancellationToken cancellationToken)
    {
        //超时接通量
        int CallInOverConnRingTime = _systemSettingCacheManager.CallInOverConnRingTime;
        //坐席超时挂断时间
        int SeatChaoTime = _systemSettingCacheManager.SeatChaoTime;
        //未接秒挂时间
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        //呼入有效时间
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        //接通秒挂时间
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;

        var callData = await _callNativeRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                //.GroupBy(p => p.CreationTime.ToString("yyyy-MM-dd"))
                .GroupBy(p => new { CreationTime = p.CreationTime.ToString("yyyy-MM-dd"), CallNo = p.CallNo })
                .Select(p => new QueryCallsDetailStatistics
                {
                    Date = p.CreationTime.ToString("yyyy-MM-dd"),
                    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.Duration == 0 && p.RingDuration <= noConnectByeTimes && p.RingDuration > 0 && p.Direction == ECallDirection.In, 1, 0)), //呼入队列挂断
                    InNotAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(p.Duration == 0 && p.TelNo != "0" && p.Direction == ECallDirection.In, 1, 0)), // 挂机量
                    IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.Direction == ECallDirection.In && p.BeginIvrTime.HasValue && !p.BeginQueueTime.HasValue && !p.BeginRingTime.HasValue && p.AnsweredTime == null, 1, 0)), //IVR挂断
                    OutConnectionQuantity = SqlFunc.AggregateSum(SqlFunc.IIF(p.AnsweredTime != null && p.Direction == ECallDirection.Out, 1, 0)), // 呼出接通量
                    OutNotAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(p.AnsweredTime == null && p.Direction == ECallDirection.Out, 1, 0)), // 呼出未接量
                })
                .OrderBy(p => p.Date)
                .ToListAsync(cancellationToken);

        return callData;
    }

    public virtual async Task<List<QueryCallsDetailDto>> QueryCallsHourDetailAsync(BiQueryCallsDto dto, CancellationToken cancellationToken)
    {
        //超时接通量
        int CallInOverConnRingTime = _systemSettingCacheManager.CallInOverConnRingTime;
        //坐席超时挂断时间
        int SeatChaoTime = _systemSettingCacheManager.SeatChaoTime;
        //未接秒挂时间
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        //呼入有效时间
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        //接通秒挂时间
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;
        return await _callNativeRepository.QueryCallsHourDetail(dto.StartTime.Value, dto.EndTime.Value, noConnectByeTimes, effectiveTimes, connectByeTimes, CallInOverConnRingTime, SeatChaoTime, dto.Line);
    }

    public virtual async Task<(int, List<QueryCallsStatisticsDetailOutDto>)> QueryCallsStatisticsDetailAsync(QueryCallsStatisticsDetailInDto dto, CancellationToken cancellationToken)
    {
        dto.FieldName ??= string.Empty;
        dto.FieldName = dto.FieldName.ToLower();
        //超时接通量
        int CallInOverConnRingTime = _systemSettingCacheManager.CallInOverConnRingTime;
        //坐席超时挂断时间
        int SeatChaoTime = _systemSettingCacheManager.SeatChaoTime;
        //未接秒挂时间
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        //呼入有效时间
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        //接通秒挂时间
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;
        var query = _callNativeRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((c, o) => c.Id == o.CallId)
            .WhereIF(dto.OrderNo.NotNullOrEmpty(), (c, o) => o.No == dto.OrderNo)
            .WhereIF(dto.FromNo.NotNullOrEmpty(), (c, o) => c.FromNo == dto.FromNo)
            .WhereIF(dto.ToNo.NotNullOrEmpty(), (c, o) => c.ToNo == dto.ToNo)
            .WhereIF(dto.TelNo.NotNullOrEmpty(), (c, o) => c.TelNo == dto.TelNo)
            .WhereIF(dto.EndBy.IsNotNull(), (c, o) => c.EndBy == dto.EndBy)
            .Where((c, o) => c.CreationTime >= dto.StartTime && c.CreationTime <= dto.EndTime);

        if (dto.FieldName == "intotal") // 呼入总量
            query = query.Where((c, o) => c.Direction == ECallDirection.In);
        if (dto.FieldName == "inconnectionquantity") // 呼入接通
            query = query.Where((c, o) => c.Direction == ECallDirection.In && c.AnsweredTime != null);
        if (dto.FieldName == "innotanswered") // 挂机量
            query = query.Where((c, o) => c.Direction == ECallDirection.In && c.Duration ==0 && c.TelNo != "0");
        if (dto.FieldName == "notacceptedhang") // 呼入队列挂断
            query = query.Where((c, o) => c.Duration == 0 && c.RingDuration <= noConnectByeTimes && c.RingDuration > 0 && c.Direction == ECallDirection.In);
        if (dto.FieldName == "ivrbyecount") // 呼入IVR挂断
            query = query.Where((c, o) => c.Direction == ECallDirection.In && c.BeginIvrTime.HasValue && !c.BeginQueueTime.HasValue && !c.BeginRingTime.HasValue && c.AnsweredTime == null);
        if (dto.FieldName == "outconnectionquantity") // 呼出接通量
            query = query.Where((c, o) => c.AnsweredTime != null && c.Direction == ECallDirection.Out);
        if (dto.FieldName == "outnotanswered") // 呼出未接通
            query = query.Where((c, o) => c.AnsweredTime == null && c.Direction == ECallDirection.Out);
        return await query
            .Select<QueryCallsStatisticsDetailOutDto>()
            .ToPagedListAsync(dto.PageIndex, dto.PageSize, cancellationToken);
    }

    /// <summary>
    /// 坐席话务统计分析
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public virtual async Task<List<BiSeatCallsDto>> QuerySeatCallAsync(ReportRequiredPagedRequest dto, CancellationToken cancellationToken)
    {
        int noConnectByeTimes = _systemSettingCacheManager.NoConnectByeTimes;
        int effectiveTimes = _systemSettingCacheManager.EffectiveTimes;
        int connectByeTimes = _systemSettingCacheManager.ConnectByeTimes;

        var list = await _userRepository.Queryable()
              .LeftJoin<CallNative>((u, c) => u.Id == c.UserId)
              .Where(u => !u.IsDeleted && u.UserType == EUserType.Seat)
              .Where((u, c) => c.CreationTime >= dto.StartTime)
              .Where((u, c) => c.CreationTime <= dto.EndTime)
              .GroupBy((u, c) => new { c.UserName, c.UserId })
              .Select((u, c) => new BiSeatCallsDto
              {
                  Name = c.UserName,
                  UserId = c.UserId,
                  InTotal = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In, 1, 0)),
                  OutTotal = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.Out, 1, 0)),
                  InAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime != null, 1, 0)),
                  OutAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.Out && c.AnsweredTime != null, 1, 0)),
                  InHangupImmediate = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime == null && c.RingDuration < noConnectByeTimes, 1, 0)),
                  InHanguped = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime == null, 1, 0)),
                  InDurationAvg = SqlFunc.Ceil(SqlFunc.AggregateAvg(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime != null, c.Duration, 0))),
                  OutDurationAvg = SqlFunc.Ceil(SqlFunc.AggregateAvg(SqlFunc.IIF(c.Direction == ECallDirection.Out && c.AnsweredTime != null, c.Duration, 0))),
                  InAvailableAnswer = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime != null && c.Duration >= effectiveTimes, 1, 0)),
                  InHangupImmediateWhenAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.Direction == ECallDirection.In && c.AnsweredTime != null && c.Duration < connectByeTimes, 1, 0)),
              })
              .MergeTable()
              .ToListAsync();

        list.ForEach(d =>
        {
            d.LoginDuration = _workRepository.Queryable().Where(q => q.UserId == d.UserId && q.CreationTime >= dto.StartTime && q.CreationTime <= dto.EndTime).Sum(q => q.WorkingDuration);
            if (d.LoginDuration != null)
            {
                d.LoginDuration = Math.Round(d.LoginDuration.Value, digits: 2);
            }
            d.RestDuration = _telRestRepository.Queryable().Where(q => q.UserId == d.UserId && q.CreationTime >= dto.StartTime && q.CreationTime <= dto.EndTime).Sum(q => q.RestDuration);
            d.RestDuration = Math.Round(d.RestDuration, digits: 2);
        });

        return list;

    }


    public virtual async Task<(int, List<BiSeatSwitchDto>)> QuerySeatSwitchAsync(QuerySeatSwitchRequest dto, CancellationToken cancellationToken)
    {
        return await _callNativeRepository.Queryable()
            .Where(x => !string.IsNullOrEmpty(x.AgentTransferNumber))
            .WhereIF(!string.IsNullOrEmpty(dto.UserName), x => x.UserName.Contains(dto.UserName))
            .WhereIF(!string.IsNullOrEmpty(dto.CDPN), x => x.ToNo.Contains(dto.CDPN))
            .Where(x => x.CreationTime >= dto.StartTime)
            .Where(x => x.CreationTime <= dto.EndTime)
            .Select(x => new BiSeatSwitchDto
            {
                UserId = x.UserId,
                CPN = x.FromNo,
                CDPN = x.ToNo,
                CreatedTime = x.CreationTime,
                TelNo = x.TelNo,
                UserName = x.UserName,
            })
            .OrderByDescending(x => x.CreatedTime)
            .ToPagedListAsync(dto.PageIndex, dto.PageSize, cancellationToken);
    }
}