using Hotline.CallCenter.Calls;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.DataPermissions;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion.Internal;
using SqlSugar;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Net;
using System.Reflection;
using System.Reflection.Emit;
using XF.Domain.Authentications;
using XF.Domain.Dependency;

namespace Hotline.Repository.SqlSugar.Orders
{
    public class OrderRepository : BaseRepositoryWorkflow<Order>, IOrderRepository, IScopeDependency
    {
        private readonly IDataPermissionFilterBuilder _dataPermissionFilterBuilder;

        public OrderRepository(ISugarUnitOfWork<HotlineDbContext> uow,
            IDataPermissionFilterBuilder dataPermissionFilterBuilder
            ) : base(uow, dataPermissionFilterBuilder)
        {
            _dataPermissionFilterBuilder = dataPermissionFilterBuilder;
        }

        public async Task<string> AddOrderNavAsync(Order order, CancellationToken cancellationToken)
        {
            //弥补AddNav方法没有自动指派到创建人
            order.AssignToCreator(_dataPermissionFilterBuilder.DataPermissionManager);

            await AddNav(order).Include(d => d.OrderExtension).ExecuteCommandAsync();

            return order.Id;
        }



        /// <summary>
        /// 工单词库批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderWrodBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderWord>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }


        /// <summary>
        /// 工单观察批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderObserveBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderObserve>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }

        /// <summary>
        /// 工单终结批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderFinalityBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderFinality>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }

        public async Task OrderScreenRevisionVisit(string VisitId, bool canHandle, CancellationToken cancellationToken)
        {

            await Db.Updateable<OrderVisit>().SetColumns(x => x.IsCanHandle == canHandle).Where(x => x.Id == VisitId).ExecuteCommandAsync(cancellationToken);
        }

        public async Task FileAsync(Order order, CancellationToken cancellationToken)
        {
            await Db.Updateable<Order>().SetColumns(x => x.FileJson == order.FileJson).Where(x => x.Id == order.Id).ExecuteCommandAsync(cancellationToken);
        }

        private static object GetPropertyValue(object obj, string property)
        {
            PropertyInfo propertyInfo = obj.GetType().GetProperty(property);
            return propertyInfo.GetValue(obj, null);
        }

        public async Task<object> HotPortJoinOrgStatistics(DateTime StartTime, DateTime EndTime, bool IsCenter, string? OrgCode)
        {
            //查询一级热点
            var hotSpotList = await Db.Queryable<Hotspot>().Where(x => string.IsNullOrEmpty(x.ParentId)).ToListAsync();
            List<dynamic>? listReturn = new List<dynamic>();
            foreach (var item in hotSpotList)
            {
                var table = await Db.Queryable<Order>()
                    .LeftJoin<SystemOrganize>((it, o) => it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                    .Where((it, o) => it.CreationTime >= StartTime && it.CreationTime <= EndTime)
                    .WhereIF(IsCenter == false, (it, o) => o.Id.StartsWith(OrgCode))
                    .GroupBy((it, o) => new
                    {
                        CurrentHandleOrgCode = (it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                        o.Name
                    })
                    .Select((it, o) => new
                    {
                        OrgCode = it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                        Count = SqlFunc.AggregateSum(SqlFunc.IIF(it.HotspotId.Substring(0, 2) == item.Id, 1, 0)),
                        Key = item.Id,
                        OrgName = o.Name,
                        HotSorpName = item.HotSpotName
                    }).ToPivotListAsync(x => x.Key, x => new { x.OrgCode, x.OrgName, x.HotSorpName }, x => x.Sum(x => x.Count));
                listReturn.AddRange(table);
            }
            return new { HotSpot = hotSpotList, Data = listReturn };
        }

        public ISugarQueryable<DepartmentalProcessingStatisticsDataDto> OrderUnionAll(ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t1,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t2,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t3,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t4,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t5,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t6)
        {
            return Db.UnionAll(t1, t2, t3, t4, t5, t6)
                       .GroupBy(p => new { p.OrgCode })
                       .Select(p => new DepartmentalProcessingStatisticsDataDto
                       {
                           OrgCode = p.OrgCode,
                           //  OrderCountNum = SqlFunc.AggregateSum(p.OrderCountNum),
                           YbOrderCountNum = SqlFunc.AggregateSum(p.YbOrderCountNum),
                           ZbOrderCountNum = SqlFunc.AggregateSum(p.ZbOrderCountNum),
                           Archived = SqlFunc.AggregateSum(p.Archived),
                           ToBeArchived = SqlFunc.AggregateSum(p.ToBeArchived),
                           WaitPublished = SqlFunc.AggregateSum(p.WaitPublished),
                           PublishedOpen = SqlFunc.AggregateSum(p.PublishedOpen),
                           PublishedNoOpen = SqlFunc.AggregateSum(p.PublishedNoOpen),
                           YbOverdue = SqlFunc.AggregateSum(p.YbOverdue),
                           ZbOverdue = SqlFunc.AggregateSum(p.ZbOverdue),
                           CompleteOnTime = SqlFunc.AggregateSum(p.CompleteOnTime),
                           HqybOverdue = SqlFunc.AggregateSum(p.HqybOverdue),
                           HqzbOverdue = SqlFunc.AggregateSum(p.HqzbOverdue),
                           DelayEnd = SqlFunc.AggregateSum(p.DelayEnd),
                           DelayWait = SqlFunc.AggregateSum(p.DelayWait),
                           OrderDelayCount = SqlFunc.AggregateSum(p.OrderDelayCount),
                           ScreenCount = SqlFunc.AggregateSum(p.ScreenCount),
                           ScreenApproval = SqlFunc.AggregateSum(p.ScreenApproval),
                           ScreenPass = SqlFunc.AggregateSum(p.ScreenPass),
                           ScreenNotPass = SqlFunc.AggregateSum(p.ScreenNotPass),
                           SatisfactionCount = SqlFunc.AggregateSum(p.SatisfactionCount),
                           NotSatisfactionCount = SqlFunc.AggregateSum(p.NotSatisfactionCount)
                       })
                       .MergeTable();


        }

        public ISugarQueryable<T> UnionAll<T>(params ISugarQueryable<T>[] queryables) where T : class
        {
            return Db.UnionAll(queryables);
        }

        /// <summary>
        /// 部门办件统计
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<DepartmentalProcessingStatisticsDataDto> DepartmentalProcessingStatisticsNew(string sql)
        {
            return Db.Ado.SqlQuery<DepartmentalProcessingStatisticsDataDto>(sql);

        }

        /// <summary>
        /// 区域分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderAreaTime(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AreaCode = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemArea>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.AreaCode)
              .Where((s, p) => s.Id.Length == 6 && s.Id != "510000")
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.AreaName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AreaCode != null && p.AreaCode != "", 1, 0)),
                  AreaCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  AreaName = s.AreaName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.AreaCode,
                   p.count
               })
                .ToPivotListAsync(p => p.AreaCode, p => p.Hour, p => p.Sum(x => x.count));
            return list;

        }

        /// <summary>
        /// 区域分时统计--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderAreaTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                   .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AreaCode = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemArea>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.AreaCode)
              .Where((s, p) => s.Id.Length == 6 && s.Id != "510000")
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.AreaName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AreaCode != null && p.AreaCode != "", 1, 0)),
                  AreaCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  AreaName = s.AreaName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.AreaName,
                  p.count
              })
               .ToPivotTableAsync(p => p.AreaName, p => p.Hour, p => p.Sum(x => x.count));

            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 热点类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderHotspotTime(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     HotspotCode = p.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<Hotspot>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.HotspotCode)
              .Where((s, p) => s.Id.Length == 2 && (s.ParentId == null || s.ParentId == ""))
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.HotSpotName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.HotspotCode != null && p.HotspotCode != "", 1, 0)),
                  HotspotCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  HotSpotName = s.HotSpotName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.HotspotCode,
                   p.count
               })
                .ToPivotListAsync(p => p.HotspotCode, p => p.Hour, p => p.Sum(x => x.count));
            return list;

        }

        /// <summary>
        /// 热点类型分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderHotspotTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     HotspotCode = p.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<Hotspot>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.HotspotCode)
              .Where((s, p) => s.Id.Length == 2 && (s.ParentId == null || s.ParentId == ""))
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.HotSpotName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.HotspotCode != null && p.HotspotCode != "", 1, 0)),
                  HotspotCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  HotSpotName = s.HotSpotName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.HotSpotName,
                  p.count
              })
               .ToPivotTableAsync(p => p.HotSpotName, p => p.Hour, p => p.Sum(x => x.count));

            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 受理类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderAcceptanceTime(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AcceptTypeCode = p.AcceptTypeCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.AcceptTypeCode)
              .Where((s, p) => s.DicTypeCode == "AcceptType")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AcceptTypeCode != null && p.AcceptTypeCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.DicDataValue,
                   p.count
               })
                .ToPivotListAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.count));
            return list;

        }

        /// <summary>
        /// 受理类型分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderAcceptanceTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                   .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AcceptTypeCode = p.AcceptTypeCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.AcceptTypeCode)
              .Where((s, p) => s.DicTypeCode == "AcceptType")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AcceptTypeCode != null && p.AcceptTypeCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.DicDataName,
                  p.count
              })
               .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));

            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 信件来源分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderSourceTime(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => new
              {
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName,
                  Hour = p.Hour,
              })
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  Count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.DicDataValue,
                   p.Count
               })
                .ToPivotListAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.Count));
            return list;

        }

        /// <summary>
        /// 信件来源分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderSourceTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.DicDataName,
                  p.count
              })
               .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));
            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 信件来源统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderSource(QueryOrderSourceRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.ToString("yyyy-MM-dd")
                 })
               .MergeTable();

            var listOrg = await Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => p.Hour)
              .Select((s, p) => new
              {
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName
              })
              .ToPivotListAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.count));
            return listOrg;

        }

        /// <summary>
        /// 信件来源统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderSourceExport(QueryOrderSourceRequest dto)
        {
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.ToString("yyyy-MM-dd")
                 })
               .MergeTable();

            var listOrg = await Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => p.Hour)
              .Select((s, p) => new
              {
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName
              })
              .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));
            listOrg.Columns["Hour"].ColumnName = "日期";
            if (listOrg.Rows[0][listOrg.Rows.Count - 1] == "0")
                listOrg.Rows[listOrg.Rows.Count - 1].Delete();
            return InitDatatTable(listOrg, dto.AddColumnName);
        }

        /// <summary>
        /// 处理导出数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="AddColumnName"></param>
        /// <returns></returns>
        public DataTable InitDatatTable(DataTable dt, List<string> AddColumnName)
        {
            //修改列名
            if (dt.Columns.Contains("Hour"))
                dt.Columns["Hour"].ColumnName = "时间段";

            //增加小计
            DataColumn totalColumn = new DataColumn("小计", typeof(decimal));
            dt.Columns.Add(totalColumn);
            if (dt.Columns.Contains("Column1"))
                dt.Columns.Remove("Column1");
            //计算小计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int sumcount = 0;
                for (int j = 1; j < dt.Columns.Count - 1; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[i][j].ToString());
                }
                dt.Rows[i][dt.Columns.Count - 1] = sumcount;
            }

            //增加合计
            DataRow totalRow = dt.NewRow();
            if (dt.Columns[0].ColumnName == "日期") totalRow["日期"] = "合计";
            else totalRow["时间段"] = "合计";
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                int sumcount = 0;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[j][i].ToString());
                }
                totalRow[i] = sumcount;
            }
            dt.Rows.Add(totalRow);

            //创建新表
            DataTable dt2 = new DataTable();

            //添加表头
            foreach (var item in AddColumnName)
            {
                if (dt.Columns.Contains(item))
                    dt2.Columns.Add(item);
            }

            //处理数据
            foreach (DataRow sourceRow in dt.Rows)
            {
                DataRow targetRow = dt2.NewRow();
                foreach (var item in AddColumnName)
                {
                    targetRow[item] = sourceRow[item];
                }
                dt2.Rows.Add(targetRow);
            }
            return dt2;
        }


        /// <summary>
        /// 热点区域明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<HotspotAndAreaStatisticsDetailDto> HotspotAndAreaStatisticsDetail(HotspotAndAreaStatisticsDetailReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var query = Db.Queryable<Order>()
                //.Includes(x => x.OrderScreens.OrderByDescending(x => x.CreationTime).FirstOrDefault())
                .Where(x => x.HotspotId.StartsWith(dto.HotspotId) && x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), x => x.AreaCode.StartsWith(dto.AreaCode))
                .Select(x => new HotspotAndAreaStatisticsDetailDto
                {
                    Id = x.Id,
                    Status = x.Status,
                    SourceChannel = x.SourceChannel,
                    ActualHandleStepName = x.ActualHandleStepName,
                    ReTransactNum = x.ReTransactNum,
                    IsUrgent = x.IsUrgent,
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(d => d.OrderId == x.Id).OrderByDesc(d => d.CreationTime).Select(d => d.Status),
                    No = x.No,
                    CreationTime = x.CreationTime,
                    Title = x.Title,
                    ExpiredTime = x.ExpiredTime,
                    ActualHandleOrgName = x.ActualHandleOrgName,
                    FiledTime = x.FiledTime,
                    ActualHandleTime = x.ActualHandleTime,
                    AcceptType = x.AcceptType,
                    HotspotName = x.HotspotName,
                    AcceptorName = x.AcceptorName,
                    NearlyExpiredTime = x.NearlyExpiredTime,
                    NearlyExpiredTimeOne = x.NearlyExpiredTimeOne
                }).OrderByDescending(x => x.CreationTime);
            return query;
        }

        /// <summary>
        /// 受理类型明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<HotspotAndAreaStatisticsDetailDto> AcceptTypeStatisticsByDateDetail(AcceptTypeStatisticsByDateDetailReq dto)
        {
            string Date = dto.Date.ToString("yyyy-MM-dd");
            var query = Db.Queryable<Order>()
               //.Includes(x => x.OrderScreens.OrderByDescending(x => x.CreationTime).FirstOrDefault())
               .Where(x => x.AcceptTypeCode == dto.AcceptTypeCode && x.CreationTime.ToString("yyyy-MM-dd") == Date)
               .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.No == dto.No)
               .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Title.Contains(dto.Title))
               .WhereIF(!string.IsNullOrEmpty(dto.CurrentHandleOrgName), x => x.ActualHandleOrgName.Contains(dto.CurrentHandleOrgName))
               .Select(x => new HotspotAndAreaStatisticsDetailDto
               {
                   Id = x.Id,
                   Status = x.Status,
                   SourceChannel = x.SourceChannel,
                   ActualHandleStepName = x.ActualHandleStepName,
                   ReTransactNum = x.ReTransactNum,
                   IsUrgent = x.IsUrgent,
                   OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(d => d.OrderId == x.Id).OrderByDesc(d => d.CreationTime).Select(d => d.Status),
                   No = x.No,
                   CreationTime = x.CreationTime,
                   Title = x.Title,
                   ExpiredTime = x.ExpiredTime,
                   ActualHandleOrgName = x.ActualHandleOrgName,
                   FiledTime = x.FiledTime,
                   ActualHandleTime = x.ActualHandleTime,
                   AcceptType = x.AcceptType,
                   HotspotName = x.HotspotName,
                   AcceptorName = x.AcceptorName,
                   NearlyExpiredTime = x.NearlyExpiredTime,
                   NearlyExpiredTimeOne = x.NearlyExpiredTimeOne
               }).OrderByDescending(x => x.CreationTime);
            return query;
        }

        /// <summary>
        /// 根据时间查询受理类型统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<(List<SystemDicData> acceptType, object items)> AcceptTypeStatisticsByDate(AcceptTypeStatisticsByDateReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            //处理时间
            var diffDays = (dto.EndTime - dto.StartTime).Days;
            List<string> dateList = new List<string>();
            for (int i = 0; i <= diffDays; i++)
            {
                dateList.Add(dto.StartTime.AddDays(i).ToString("yyyy-MM-dd"));
            }
            //第一列
            var dateQuery = Db.Reportable(dateList).ToQueryable<string>().MergeTable();

            //表头
            var acceptTypeList = Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).MergeTable();

            //工单
            var orderList = Db.Queryable<Order>()
                .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
                .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.TransferPhone == dto.Gateway)
                .Select(x => new
                {
                    Date = x.CreationTime.ToString("yyyy-MM-dd"),
                    AcceptTypeCode = x.AcceptTypeCode,
                })
                .MergeTable();

            //时间关联工单
            var dateAndOrderList = dateQuery.LeftJoin(orderList, (it, o) => it.ColumnName == o.Date)
                .Where((it, o) => string.IsNullOrEmpty(o.AcceptTypeCode) == false)
                .GroupBy((it, o) => new
                {
                    it.ColumnName,
                    o.AcceptTypeCode,
                })
                .Select((it, o) => new
                {
                    Date = it.ColumnName,
                    AcceptTypeCode = o.AcceptTypeCode,
                    Count = SqlFunc.AggregateCount(o.AcceptTypeCode)
                }).MergeTable();
            //类型关联工单
            var returnList = await acceptTypeList.LeftJoin(dateAndOrderList, (it, o) => it.DicDataValue == o.AcceptTypeCode)
                .Where((it, o) => o.Date != null)
                .GroupBy((it, o) => new
                {
                    o.Date,
                    it.DicDataValue,
                })
                .OrderBy((it, o) => o.Date)
                .Select((it, o) => new
                {
                    Date = o.Date,
                    AcceptTypeCode = it.DicDataValue,
                    Count = SqlFunc.AggregateSum(o.Count)
                })
                .ToPivotListAsync(q => q.AcceptTypeCode, q => new { q.Date }, q => q.Sum(x => x.Count));

            //表头
            var acceptTypeTitleList = await Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).ToListAsync();
            return (acceptTypeTitleList, returnList);
        }

        /// <summary>
        /// 根据时间查询受理类型统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> AcceptTypeStatisticsByDateExport(AcceptTypeStatisticsByDateReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            //处理时间
            var diffDays = (dto.EndTime - dto.StartTime).Days;
            List<string> dateList = new List<string>();
            for (int i = 0; i <= diffDays; i++)
            {
                dateList.Add(dto.StartTime.AddDays(i).ToString("yyyy-MM-dd"));
            }
            //第一列
            var dateQuery = Db.Reportable(dateList).ToQueryable<string>().MergeTable();

            //表头
            var acceptTypeList = Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).MergeTable();

            //工单
            var orderList = Db.Queryable<Order>()
                .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
                .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.TransferPhone == dto.Gateway)
                .Select(x => new
                {
                    Date = x.CreationTime.ToString("yyyy-MM-dd"),
                    AcceptTypeCode = x.AcceptTypeCode,
                })
                .MergeTable();

            //时间关联工单
            var dateAndOrderList = dateQuery.LeftJoin(orderList, (it, o) => it.ColumnName == o.Date)
                .Where((it, o) => string.IsNullOrEmpty(o.AcceptTypeCode) == false)
                .GroupBy((it, o) => new
                {
                    it.ColumnName,
                    o.AcceptTypeCode,
                })
                .Select((it, o) => new
                {
                    Date = it.ColumnName,
                    AcceptTypeCode = o.AcceptTypeCode,
                    Count = SqlFunc.AggregateCount(o.AcceptTypeCode)
                }).MergeTable();
            //类型关联工单
            var returnList = await acceptTypeList.LeftJoin(dateAndOrderList, (it, o) => it.DicDataValue == o.AcceptTypeCode)
                .Where((it, o) => o.Date != null)
                .GroupBy((it, o) => new
                {
                    o.Date,
                    it.DicDataValue,
                    it.DicDataName
                })
                .OrderBy((it, o) => o.Date)
                .Select((it, o) => new
                {
                    Date = o.Date,
                    AcceptTypeCode = it.DicDataValue,
                    AcceptType = it.DicDataName,
                    Count = SqlFunc.AggregateSum(o.Count)
                })
                .ToPivotTableAsync(q => q.AcceptType, q => new { q.Date }, q => q.Sum(x => x.Count));

            return returnList;
        }

        public DataTable InitAcceptTypeTable(DataTable dt, List<string> AddColumnName)
        {
            //修改列名
            if (dt.Columns.Contains("Date"))
                dt.Columns["Date"].ColumnName = "日期";

            //增加小计
            DataColumn totalColumn = new DataColumn("小计", typeof(decimal));
            dt.Columns.Add(totalColumn);
            if (dt.Columns.Contains("Column1"))
                dt.Columns.Remove("Column1");

            //计算小计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int sumcount = 0;
                for (int j = 1; j < dt.Columns.Count - 1; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[i][j].ToString());
                }
                dt.Rows[i][dt.Columns.Count - 1] = sumcount;
            }

            //增加合计
            DataRow totalRow = dt.NewRow();
            if (dt.Columns[0].ColumnName == "Date") totalRow["Date"] = "合计";
            else totalRow["日期"] = "合计";
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                int sumcount = 0;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[j][i].ToString());
                }
                totalRow[i] = sumcount;
            }
            dt.Rows.Add(totalRow);

            //创建新表
            DataTable dt2 = new DataTable();

            //添加表头
            foreach (var item in AddColumnName)
            {
                dt2.Columns.Add(item);
            }

            //处理数据
            foreach (DataRow sourceRow in dt.Rows)
            {
                DataRow targetRow = dt2.NewRow();
                foreach (var item in AddColumnName)
                {
                    try
                    {
                        targetRow[item] = sourceRow[item];
                    }
                    catch
                    {
                        targetRow[item] = 0;
                    }
                }
                dt2.Rows.Add(targetRow);
            }

            return dt2;
        }

        public ISugarQueryable<SelectOrderId> OrderListUnionAll(ISugarQueryable<SelectOrderId> t1, ISugarQueryable<SelectOrderId> t2)
        {
            return Db.UnionAll(t1, t2).Select(it => new SelectOrderId { Id = it.Id }).MergeTable();
        }
    }

    public class OrderScreenRepository : BaseRepositoryWorkflow<OrderScreen>, IOrderScreenRepository, IScopeDependency
    {
        public OrderScreenRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
        {
        }
    }

    public class OrderDelayRepository : BaseRepositoryWorkflow<OrderDelay>, IOrderDelayRepository, IScopeDependency
    {
        public OrderDelayRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
        {
        }
    }

}
