hotline/src/Hotline.Api/Controllers/Bi/BiCallController.cs

using Hotline.Caching.Interfaces;
using Hotline.Caching.Services;
using Hotline.CallCenter.Calls;
using Hotline.CallCenter.Tels;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Settings;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.User;
using Hotline.Share.Requests;
using Hotline.Tools;
using Hotline.Users;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System.Data;
using XF.Domain.Constants;
using XF.Domain.Exceptions;
using XF.Domain.Repository;

namespace Hotline.Api.Controllers.Bi;

/// <summary>
/// 话务报表
/// </summary>
public class BiCallController : BaseController
{
    private readonly IRepository<TrCallRecord> _trCallRecordRepository;
    private readonly IRepository<User> _userRepository;
    private readonly IRepository<TelRest> _telRestRepository;
    private readonly IMapper _mapper;
    private readonly ITrCallRecordRepository _trCallRecordRepositoryEx;
    private readonly ISystemSettingCacheManager _systemSettingCacheManager;
    private readonly IRepository<Work> _workRepository;
    private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;



    public BiCallController(
        IRepository<TrCallRecord> trCallRecordRepository,
        IRepository<User> userRepository,
        IRepository<TelRest> telRestRepository,
        IMapper mapper,
        ITrCallRecordRepository trCallRecordRepositoryEx,
        ISystemSettingCacheManager systemSettingCacheManager,
        ISystemDicDataCacheManager sysDicDataCacheManager,
        IRepository<Work> workRepository)
    {
        _trCallRecordRepository = trCallRecordRepository;
        _userRepository = userRepository;
        _telRestRepository = telRestRepository;
        _mapper = mapper;
        _trCallRecordRepositoryEx = trCallRecordRepositoryEx;
        _systemSettingCacheManager = systemSettingCacheManager;
        _workRepository = workRepository;
        _sysDicDataCacheManager = sysDicDataCacheManager;

    }

    /// <summary>
    /// 话务统计分析
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    [HttpGet("calls")]
    [AllowAnonymous]
    public async Task<List<BiCallDto>> QueryCallsAsync([FromQuery] BiQueryCallsDto dto)
    {
        if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
        #region 注释
        //var items = await _trCallRecordRepository.Queryable()
        //    .Where(d => d.CreatedTime >= dto.StartTime && d.CreatedTime <= dto.EndTime)
        //    .Where(d => d.CallDirection == ECallDirection.In)
        //    .WhereIF(!string.IsNullOrEmpty(dto.Line), d => d.Gateway == dto.Line)
        //    .Select(d => new
        //    {
        //        d.CreatedTime.Year,
        //        d.CreatedTime.Month,
        //        d.CreatedTime.Day,
        //        d.CreatedTime.Hour,
        //        d.AnsweredTime,
        //        d.EndBy
        //    })
        //    .MergeTable()
        //    .GroupBy(d => new
        //    {
        //        d.Year,
        //        d.Month,
        //        d.Day,
        //        d.Hour
        //    })
        //    .Select(d => new BiCallDto
        //    {
        //        Hour = d.Hour,
        //        Total = SqlFunc.AggregateCount(d.Hour),
        //        Answered = SqlFunc.AggregateSum(SqlFunc.IIF(d.AnsweredTime != null, 1, 0)),
        //        Hanguped = SqlFunc.AggregateSum(SqlFunc.IIF(d.AnsweredTime == null && d.EndBy != null && d.EndBy.Value == EEndBy.To, 1, 0))
        //    })
        //    .OrderBy(d => d.Hour)
        //    .ToListAsync(HttpContext.RequestAborted);

        //if (items.Count < 24)
        //{
        //    for (var i = 0; i < 24; i++)
        //    {
        //        var item = items.FirstOrDefault(d => d.Hour == i);
        //        if (item is null)
        //            items.Add(new BiCallDto { Hour = i });
        //    }

        //    items = items.OrderBy(d => d.Hour).ToList();
        //}
        #endregion
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        return await _trCallRecordRepositoryEx.GetQueryCalls(dto.StartTime.Value, dto.EndTime.Value);

    }

    /// <summary>
    /// 话务统计分析---导出
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    [HttpPost("calls_export")]
    [AllowAnonymous]
    public async Task<FileStreamResult> ExportQueryCallsAsync([FromBody] ExportExcelDto<BiQueryCallsDto> dto)
    {
        if (!dto.QueryDto.StartTime.HasValue || !dto.QueryDto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

        dto.QueryDto.EndTime = dto.QueryDto.EndTime.Value.AddDays(1).AddSeconds(-1);

        var list = await _trCallRecordRepositoryEx.GetQueryCalls(dto.QueryDto.StartTime.Value, dto.QueryDto.EndTime.Value);

        if (list != null && list.Count > 0)
        {
            list.Add(new BiCallDto()
            {
                HourRange = "合计",
                Hour = 13,
                Total = list.Sum(p => p.Total),
                Answered = list.Sum(p => p.Answered),
                Hanguped = list.Sum(p => p.Hanguped)
            });
        }


        dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

        var dtos = list
            .Select(stu => _mapper.Map(stu, typeof(BiCallDto), dynamicClass))
            .Cast<object>()
            .ToList();

        var stream = ExcelHelper.CreateStream(dtos);

        return ExcelStreamResult(stream, "话务统计分析");
    }


    /// <summary>
    /// 坐席话务统计分析
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    [HttpGet("seats")]
    [AllowAnonymous]
    public async Task<IReadOnlyList<BiSeatCallsDto>> QuerySeatCallsAsync([FromQuery] ReportPagedRequest dto)
    {
        if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        //获取配置
        int noConnectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.NoConnectByeTimes)?.SettingValue[0]);
        int effectiveTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.EffectiveTimes)?.SettingValue[0]);
        int connectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.ConnectByeTimes)?.SettingValue[0]);

        var list = await _userRepository.Queryable()
              .LeftJoin<TrCallRecord>((u, c) => u.Id == c.UserId)
              .Where(u => !u.IsDeleted && u.UserType == EUserType.Seat)
              .WhereIF(dto.StartTime.HasValue, (u, c) => c.CreatedTime >= dto.StartTime.Value)
              .WhereIF(dto.EndTime.HasValue, (u, c) => c.CreatedTime <= dto.EndTime.Value)
              .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.CallDirection == ECallDirection.In, 1, 0)),
                  OutTotal = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.Out, 1, 0)),
                  InAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime != null, 1, 0)),
                  OutAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.Out && c.AnsweredTime != null, 1, 0)),
                  InHangupImmediate = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime == null && c.RingTimes < noConnectByeTimes, 1, 0)),
                  InHanguped = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime == null, 1, 0)),
                  InDurationAvg = SqlFunc.Ceil(SqlFunc.AggregateAvg(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime != null, c.Duration, 0))),
                  OutDurationAvg = SqlFunc.Ceil(SqlFunc.AggregateAvg(SqlFunc.IIF(c.CallDirection == ECallDirection.Out && c.AnsweredTime != null, c.Duration, 0))),
                  InAvailableAnswer = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime != null && c.Duration >= effectiveTimes, 1, 0)),
                  InHangupImmediateWhenAnswered = SqlFunc.AggregateSum(SqlFunc.IIF(c.CallDirection == ECallDirection.In && c.AnsweredTime != null && c.Duration < connectByeTimes, 1, 0)),
              })
              .MergeTable()
              .ToListAsync(HttpContext.RequestAborted);


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

    /// <summary>
    /// 小休统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    [HttpGet("rests")]
    [AllowAnonymous]
    public async Task<IReadOnlyList<BiSeatRestDto>> QuerySeatRest([FromQuery] QuerySeatRestRequest dto)
    {
        if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        return await _telRestRepository.Queryable()
            .WhereIF(!string.IsNullOrEmpty(dto.UserName), x => x.UserName.Contains(dto.UserName))
            .WhereIF(!string.IsNullOrEmpty(dto.StaffNo), x => x.StaffNo.Contains(dto.StaffNo))
            .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime.Value)
            .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime.Value)
            .GroupBy(x => new { x.UserId, x.StaffNo, x.UserName })
            .Select(x => new BiSeatRestDto
            {
                UserId = x.UserId,
                StaffNo = x.StaffNo,
                UserName = x.UserName,
                RestCount = SqlFunc.AggregateCount(x.Id),
                RestDuration = SqlFunc.AggregateSum(x.RestDuration / 60) / SqlFunc.AggregateCount(x.Id)
            })
            .OrderByIF(dto.SortRule is 0, a => a.RestDuration, OrderByType.Asc)
            .OrderByIF(dto.SortRule is 1, a => a.RestDuration, OrderByType.Desc)
            .MergeTable()
            .ToListAsync(HttpContext.RequestAborted);
    }

    /// <summary>
    /// 坐席转接统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    [HttpGet("seatswitch")]
    [AllowAnonymous]
    public async Task<PagedDto<BiSeatSwitchDto>> QuerySeatSwitch([FromQuery] QuerySeatSwitchRequest dto)
    {
        if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        var (total, items) = await _trCallRecordRepository.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.CDPN.Contains(dto.CDPN))
            .WhereIF(dto.StartTime.HasValue, x => x.CreatedTime >= dto.StartTime.Value)
            .WhereIF(dto.EndTime.HasValue, x => x.CreatedTime <= dto.EndTime.Value)
            .Select(x => new BiSeatSwitchDto
            {
                UserId = x.UserId,
                CPN = x.CPN,
                CDPN = x.CDPN,
                CreatedTime = x.CreatedTime,
                TelNo = x.AgentTransferNumber,
                UserName = x.UserName,
            })
            .OrderByDescending(x => x.CreatedTime)
            .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

        return new PagedDto<BiSeatSwitchDto>(total, _mapper.Map<IReadOnlyList<BiSeatSwitchDto>>(items));
    }

    /// <summary>
    /// 小时统计
    /// </summary>
    /// <returns></returns>
    [HttpGet("hourcall")]
    [AllowAnonymous]
    public async Task<List<TrCallHourDto>> QueryHourCall([FromQuery] DateTime beginDate, DateTime? endDate, string source)
    {
        //获取配置
        int noConnectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.NoConnectByeTimes)?.SettingValue[0]);
        int effectiveTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.EffectiveTimes)?.SettingValue[0]);
        int connectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.ConnectByeTimes)?.SettingValue[0]);
        var list = await _trCallRecordRepositoryEx.GetCallHourList(beginDate, endDate, noConnectByeTimes, effectiveTimes, connectByeTimes, source);
        return list;
    }

    /// <summary>
    /// 通话时段统计明细
    /// </summary>
    /// <returns></returns>
    [HttpGet("hourcall_list")]
    public async Task<object> QueryCallList([FromQuery] DateTime beginDate, DateTime? endDate, string type, string source, TimeSpan? startHourTo, int pageIndex, int pageSize)
    {
        //获取配置
        int noConnectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.NoConnectByeTimes)?.SettingValue[0]);
        int effectiveTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.EffectiveTimes)?.SettingValue[0]);
        int connectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.ConnectByeTimes)?.SettingValue[0]);
        var list = await _trCallRecordRepositoryEx.GetCallList(beginDate, endDate, noConnectByeTimes, effectiveTimes, connectByeTimes, type, source, startHourTo, pageIndex, pageSize);
        return list;
    }

    /// <summary>
    /// 通话时段统计明细获取基本信息
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    [HttpGet("hourcall_list_base")]
    public async Task<object> ReTransactBaseData()
    {
        var rsp = new
        {
            CallForwardingSource = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.CallForwardingSource),
            CallForwardingType = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.CallForwardingType),
        };
        return rsp;
    }

    /// <summary>
    /// 热线号码统计
    /// </summary>
    /// <param name="StartDate"></param>
    /// <param name="EndDate"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [HttpGet("gateway-query")]
    public async Task<List<CallHotLineDto>> QueryGateWay(DateTime beginDate, DateTime endDate, string gateway)
    {
        //获取配置
        int noConnectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.NoConnectByeTimes)?.SettingValue[0]);
        int effectiveTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.EffectiveTimes)?.SettingValue[0]);
        int connectByeTimes = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.ConnectByeTimes)?.SettingValue[0]);
        int ringTims = int.Parse(_systemSettingCacheManager.GetSetting(SettingConstants.RingTims)?.SettingValue[0]);
        var list = await _trCallRecordRepositoryEx.GetCallHotLineList(beginDate, endDate, gateway, noConnectByeTimes, effectiveTimes, connectByeTimes, ringTims);
        return list;
    }
}