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

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

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

        public async Task<List<TrCallHourDto>?> GetCallHourList(DateTime beginDate,DateTime? endDate,int noConnectByeTimes,int effectiveTimes)
        {
            //List<DateTime> dts = new List<DateTime>() { beginDate.Date.AddDays(1) };
            //for (int i = 0; i < 24; 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))
            //    .Where((it, o) => o.CreatedTime >= beginDate)
            //    .WhereIF(endDate.HasValue, (it, o) => o.CreatedTime <= endDate.Value.Date.AddDays(1).AddMilliseconds(-1))
            //    .WhereIF(!endDate.HasValue, (it, o) => o.CreatedTime <= beginDate.Date.AddDays(1).AddMilliseconds(-1))
            //    .GroupBy(it => it.ColumnName)
            //    .OrderBy(it => it.ColumnName)
            //    .Select((it, o) => new TrCallHourDto()
            //    {
            //        Dates = it.ColumnName,
            //        Hour = it.ColumnName.Hour, //小时段
            //        Count = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In, 1, 0)), //呼入总量
            //        EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.Duration >= 15, 1, 0)),//有效接通
            //        ConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.Duration > 0 && o.Duration <= 5, 1, 0)), //接通秒挂
            //        NoConnectByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.Duration == 0 && o.RingTimes <= 5, 1, 0)), //未接通秒挂
            //        QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.QueueTims > 0 && o.RingTimes == 0, 1, 0)), //队列挂断
            //        IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.BeginIvrTime.HasValue && !o.BeginQueueTime.HasValue && !o.BeginRingTime.HasValue && o.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
            //    }).ToListAsync();
            //计算小时差
            //if (!endDate.HasValue)
            //{
            //    endDate = beginDate.Date.AddDays(1).AddMilliseconds(-1);
            //}
            //TimeSpan timeDifference = endDate.Value.Subtract(beginDate).Duration();

            //int hourDiff = (int)(timeDifference.TotalHours);
            //if (!endDate.HasValue)
            //{
            //    endDate = beginDate.Date.AddDays(1).AddMilliseconds(-1);
            //}
            ////计算时间差
            //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))
            //    .GroupBy(it => it.ColumnName)
            //    .OrderBy(it => it.ColumnName)
            //    .Select((it, o) => new TrCallHourDto()
            //    {
            //        Dates = it.ColumnName,
            //        //Hour = it.ColumnName.Hour,
            //        Count = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In, 1, 0)), //呼入总量
            //    })
            //    .ToListAsync();

            List<DateTime> dts = new List<DateTime>() { beginDate.Date.AddDays(1) };
            for (int i = 0; i < 24; i++)//搞出所有时间断
            {
                dts.Add(dts.Last().AddHours(-1));
            }

            var list = Db.Reportable(dts).ToQueryable<DateTime>()
                .LeftJoin<TrCallRecord>((it, o) => o.CreatedTime >= it.ColumnName && o.CreatedTime <= it.ColumnName.AddHours(1))
                .GroupBy(it => it.ColumnName)
                .OrderBy(it => it.ColumnName)
                .Select((it, o) => new TrCallHourDto()
                {
                    Dates = it.ColumnName,
                    Count = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In,1,0))
                }).ToList();


            return list;
        }
    }
}