data-sharing/src/DataSharing.Host/Controllers/HotlineWebController.cs

using DataSharing.CityStateDataExchange;
using DataSharing.FwDataExchange;
using DataSharing.HotlineWeb;
using DataSharing.Police110.DaoShu110;
using DataSharing.Province;
using DataSharing.Province.SendTask;
using DataSharing.Province.SendTask.ProvinceOther;
using DataSharing.Province.SendTask.SubmitCaseInfo;
using DataSharing.Province.SendTask.SubmitCaseProcess;
using DataSharing.Province.SendTask.SubmitCaseRecord;
using DataSharing.Province.SendTask.SubmitCaseResult;
using DataSharing.RawData;
using DataSharing.SendTask;
using DataSharing.SendTask.OtherPlatforms;
using DataSharing.Share.Consts;
using DataSharing.Share.Dtos;
using DataSharing.Share.Dtos.Common;
using DataSharing.Share.Dtos.FwDataExchange;
using DataSharing.Share.Dtos.HotlineSetting;
using DataSharing.Share.Dtos.HotlineWeb;
using DataSharing.Share.Dtos.Statistics;
using DataSharing.Share.Enums;
using DataSharing.Share.Requests;
using DataSharing.YiBin.ConvergenceMedia;
using DataSharing.YiBin.Enterprise;
using DotNetCore.CAP;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using MapsterMapper;
using MediatR;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Org.BouncyCastle.Utilities;
using Serilog.Filters;
using SqlSugar;
using StackExchange.Redis;
using System.Security.Cryptography;
using System.Text;
using XC.RSAUtil;
using XF.Domain.Cache;
using XF.Domain.Exceptions;
using XF.Domain.Repository;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace DataSharing.Host.Controllers
{
    /// <summary>
    /// 
    /// </summary>
    public class HotlineWebController : BaseController
    {
        #region 注入
        private const string PublicKey = @"-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAgw+/x6IQPkH0A4eoF63j
kLThsOXWyNBdcL9LATGy/G1yTHOr1RyKJB//iNug+V8DIoIHuFTlhgLHDbSqxvRW
MONxIIF289riS6bDI4Ox/pFmOfmElFRk0lKGihaTE2Aefd6g/N+RfLLaHWztY+/v
oVeDTiOIw9y3tokIxjKwuJ/mQ66MkKh78AqQjjSD/3jcBP8ZhMyCJOK9XQcqvhD6
WBFWkxlAqKOWggDU7YohfrbNkg3bd0oGE6zCE2EHhkcQbzGCh3lu1zf4TfKMXD+P
Prr5JWDNYQTXFQklqgae+Puge7xxZGYRoi5YpIUnkQGm6zpPxhIOdxlz+Yb5geSJ
UQIDAQAB
-----END PUBLIC KEY-----";
        private const string PrivateKey = @"-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAgw+/x6IQPkH0A4eoF63jkLThsOXWyNBdcL9LATGy/G1yTHOr
1RyKJB//iNug+V8DIoIHuFTlhgLHDbSqxvRWMONxIIF289riS6bDI4Ox/pFmOfmE
lFRk0lKGihaTE2Aefd6g/N+RfLLaHWztY+/voVeDTiOIw9y3tokIxjKwuJ/mQ66M
kKh78AqQjjSD/3jcBP8ZhMyCJOK9XQcqvhD6WBFWkxlAqKOWggDU7YohfrbNkg3b
d0oGE6zCE2EHhkcQbzGCh3lu1zf4TfKMXD+PPrr5JWDNYQTXFQklqgae+Puge7xx
ZGYRoi5YpIUnkQGm6zpPxhIOdxlz+Yb5geSJUQIDAQABAoIBAAe+312BKUbsp0BV
fOyCqoTLqTJHBcBneWY86pte9bjaZQYPU1PsdF452o8a38gXwpErBcwcjwCyWv48
iQKUv8qdiSWGe+Jh3hGM1lKGfsyl3i3wnlJqgcI2U1zc2a4BoREHnmm4yBnfs2lI
HegpRCG7u3cGPZfJtxJyxdHPfsc3wk8sXt+Byqkuym87l0a8wEF7b33f2hP2R7m/
37qYvTaGX3yN4s1rssQ3wBEfHEsesQmrQrEkRIUnWfgpkH3FdR5T1V4USC6GhM6x
sIxqwBBYLL2LhVcNEifegqHtLZOw0VfUfpvoKnH5omSjUP5X+c8dmY9KPoKGpYFT
l09fiRMCgYEAwSAmJr8G/xOCPtkRZ9qpxOwAeqA9iEhB3ViIydESrYMZGUaIKAWV
ofOuDIdaMgW/2hyQtJasAnlX87Za7iODcBAMMAPSNiLEXk9nHWO+Xw/oMakgS5Dg
uE2J02Bn4KFTCGALtfo70A40kkikkR2UdD4RG6j+DgI2JxxrCgaKiIMCgYEArbrv
cvJs62LlvOFdVCY3mWEJkoBbStHGq2GdtZ5Rx9oNqiy3j/xT7Wav061OPCaC2rcd
ALEH7AA3JJtAMr6QRv3HYyd2NIrlqS6pn4tInIpqlarrnVkkOk0WhsWVkScQJyx6
eLKQ24zEF3wHslrRpHcclCWjtQ52TmWOuUvy9psCgYAWw6BbntbHSFho6hNIJ5kt
Uhg4XB0ErGe/HIl7KH7IHxTdStgPx6C8p0mTxnyWOuFDZ2yTLNN3sy/v1UXgniUH
F40oN7sWkICPEVL5PuGYuPpIqEPbRGeIsfMWDF4SN1HfQdr/h1B3lMUTnSZwyIjQ
LuS7Wu8fXZlegNYJJ3462wKBgE5TSF9vktGw/djhVj15GXoKONGXExGxcDuWQA9A
+Kf81EpT7NJ6tbADquPpb6tIxZgsFGRabDLnifT0FcaLCesnwXwfr3hS4uYaxpjK
qAsDkLg3nhCLvvyWAsDyVdNiZDL1J6ZBA3Qoi8P2xFWSApB+ryDPs3YOtiH0QZui
9UBfAoGBAIQ2uv4J7ql++EpNNtLzOhTEpyjWS+qRSZRRXfKN2lm7e7czOsSpKIJx
Q9PP8NTEmKqdI3WVFYqW/OlOFC6sjiscTOOn9Tc5Mrcn8ocCjAPjkhkCCVRMiJnv
jxrWXHbT1FB6DqkdOnBbQqS1Azqz5HxLlSyEK3F60e3SgB5iZsDZ
-----END RSA PRIVATE KEY-----";
        private readonly IMapper _mapper;
        private readonly IMediator _mediator;
        private readonly ICapPublisher _capPublisher;
        private readonly IRepository<DsOrderSend> _dsOrderSendRepository;
        private readonly IRepository<DsTelCall> _dsTelCallRepository;
        private readonly IRepository<ConfigurationInformation> _configurationInformationRepository;
        private readonly ITypedCache<ConfigurationInformationDto> _configurationInformationCache;
        private readonly IRepository<DsOrderVisitSend> _dsOrderVisitSendRepository;
        private readonly IRepository<DsKnowledgeRawData> _knowledgeRawDataRepository;
        private readonly IRepository<DsUserTokenInfo> _dsUserTokenInfoRepository;
        private readonly IRepository<DsWaitSendTaskProvinceOther> _waitSendTaskProvinceOtherRepository;
        private readonly IRepository<DsSendTaskProvinceOther> _sendTaskProvinceOtherRepository;
        private readonly IRepository<DsWaitSendTaskSubmitCaseInfo> _waitSendTaskSubmitCaseInfoRepository;
        private readonly IRepository<DsSendTaskSubmitCaseInfo> _sendTaskSubmitCaseInfoRepository;
        private readonly IRepository<DsWaitSendTaskSubmitCaseProcess> _waitSendTaskSubmitCaseProcessRepository;
        private readonly IRepository<DsSendTaskSubmitCaseProcess> _sendTaskSubmitCaseProcessRepository;
        private readonly IRepository<DsWaitSendTaskSubmitCaseRecord> _waitSendTaskSubmitCaseRecordRepository;
        private readonly IRepository<DsSendTaskSubmitCaseRecord> _sendTaskSubmitCaseRecordRepository;
        private readonly IRepository<DsWaitSendTaskSubmitCaseResult> _waitSendTaskSubmitCaseResultRepository;
        private readonly IRepository<DsSendTaskSubmitCaseResult> _sendTaskSubmitCaseResultRepository;
        private readonly IWaitSendTaskRepository _waitSendTaskRepository;
        private readonly IRepository<DsSendTaskDetailInfo> _sendTaskDetailInfoRepository;
        private readonly IRepository<DsWaitSendTaskOtherPlatforms> _waitSendTaskOtherPlatformsRepository;
        private readonly IRepository<DsSendTaskOtherPlatforms> _sendTaskOtherPlatformsRepository;
        private readonly IInitPushDataService _initPushDataService;
        private readonly IRepository<DsSendTask> _taskRepository;
        private readonly IRepository<DsSendTaskInfo> _taskInfoRepository;
        private readonly IRepository<DsOrder> _dsOrderRepository;
        private readonly IRepository<SendHotlineErrorData> _sendHotlineErrorDataRepository;
        private readonly FwClient _fwClient;
        private readonly ISharingConfigurationManager _sharingConfigurationManager;
        private readonly IRepository<UserOperationLog> _userOperationLogRepository;
        private readonly IRepository<SendFwErrorData> _sendFwErrorDataRepository;
        private readonly IRepository<DsGetCaseMaterialInfo> _getCaseMaterialInfoRepository;
        private readonly IRepository<DsReceiveDataExchangeData> _dsReceiveDataExchangeDataRepository;
        private readonly IRepository<DsReceiveEnterpriseData> _dsReceiveEnterpriseDataRepository;
        private readonly IRepository<DsReceiveMediaData> _dsReceiveMediaDataRepository;
        private readonly IRepository<DsReceivePoliceData> _dsReceivePoliceDataRepository;
        private readonly IRepository<DsReceiveHandleData> _dsReceiveHandleDataRepository;


        public HotlineWebController(IMapper mapper,
            IMediator mediator,
            ICapPublisher capPublisher,
            IRepository<DsOrderSend> dsOrderSendRepository,
            IRepository<DsTelCall> dsTelCallRepository,
            IRepository<ConfigurationInformation> configurationInformationRepository,
            ITypedCache<ConfigurationInformationDto> configurationInformationCache,
            IRepository<DsOrderVisitSend> dsOrderVisitSendRepository,
            IRepository<DsKnowledgeRawData> knowledgeRawDataRepository,
            IRepository<DsUserTokenInfo> dsUserTokenInfoRepository,
            IRepository<DsWaitSendTaskProvinceOther> waitSendTaskProvinceOtherRepository,
            IRepository<DsSendTaskProvinceOther> sendTaskProvinceOtherRepository,
            IRepository<DsWaitSendTaskSubmitCaseInfo> waitSendTaskSubmitCaseInfoRepository,
            IRepository<DsSendTaskSubmitCaseInfo> sendTaskSubmitCaseInfoRepository,
            IRepository<DsWaitSendTaskSubmitCaseProcess> waitSendTaskSubmitCaseProcessRepository,
            IRepository<DsSendTaskSubmitCaseProcess> sendTaskSubmitCaseProcessRepository,
            IRepository<DsWaitSendTaskSubmitCaseRecord> waitSendTaskSubmitCaseRecordRepository,
            IRepository<DsSendTaskSubmitCaseRecord> sendTaskSubmitCaseRecordRepository,
            IRepository<DsWaitSendTaskSubmitCaseResult> waitSendTaskSubmitCaseResultRepository,
            IRepository<DsSendTaskSubmitCaseResult> sendTaskSubmitCaseResultRepository,
           IWaitSendTaskRepository waitSendTaskRepository,
           IRepository<DsSendTaskDetailInfo> sendTaskDetailInfoRepository,
           IRepository<DsWaitSendTaskOtherPlatforms> waitSendTaskOtherPlatformsRepository,
           IRepository<DsSendTaskOtherPlatforms> sendTaskOtherPlatformsRepository,
           IInitPushDataService initPushDataService,
           IRepository<DsSendTask> taskRepository,
           IRepository<DsSendTaskInfo> taskInfoRepository,
           IRepository<DsOrder> dsOrderRepository,
           IRepository<SendHotlineErrorData> sendHotlineErrorDataRepository,
           FwClient fwClient,
           ISharingConfigurationManager sharingConfigurationManager,
           IRepository<UserOperationLog> userOperationLogRepository,
           IRepository<SendFwErrorData> sendFwErrorDataRepository,
           IRepository<DsGetCaseMaterialInfo> getCaseMaterialInfoRepository,
           IRepository<DsReceiveDataExchangeData> dsReceiveDataExchangeDataRepository,
           IRepository<DsReceiveEnterpriseData> dsReceiveEnterpriseDataRepository,
           IRepository<DsReceiveMediaData> dsReceiveMediaDataRepository,
           IRepository<DsReceivePoliceData> dsReceivePoliceDataRepository,
           IRepository<DsReceiveHandleData> dsReceiveHandleDataRepository
           )
        {
            _mapper = mapper;
            _mediator = mediator;
            _capPublisher = capPublisher;
            _dsOrderSendRepository = dsOrderSendRepository;
            _dsTelCallRepository = dsTelCallRepository;
            _configurationInformationRepository = configurationInformationRepository;
            _configurationInformationCache = configurationInformationCache;
            _dsOrderVisitSendRepository = dsOrderVisitSendRepository;
            _knowledgeRawDataRepository = knowledgeRawDataRepository;
            _dsUserTokenInfoRepository = dsUserTokenInfoRepository;
            _waitSendTaskProvinceOtherRepository = waitSendTaskProvinceOtherRepository;
            _sendTaskProvinceOtherRepository = sendTaskProvinceOtherRepository;
            _waitSendTaskSubmitCaseInfoRepository = waitSendTaskSubmitCaseInfoRepository;
            _sendTaskSubmitCaseInfoRepository = sendTaskSubmitCaseInfoRepository;
            _waitSendTaskSubmitCaseProcessRepository = waitSendTaskSubmitCaseProcessRepository;
            _sendTaskSubmitCaseProcessRepository = sendTaskSubmitCaseProcessRepository;
            _waitSendTaskSubmitCaseRecordRepository = waitSendTaskSubmitCaseRecordRepository;
            _sendTaskSubmitCaseRecordRepository = sendTaskSubmitCaseRecordRepository;
            _waitSendTaskSubmitCaseResultRepository = waitSendTaskSubmitCaseResultRepository;
            _sendTaskSubmitCaseResultRepository = sendTaskSubmitCaseResultRepository;
            _waitSendTaskRepository = waitSendTaskRepository;
            _sendTaskDetailInfoRepository = sendTaskDetailInfoRepository;
            _waitSendTaskOtherPlatformsRepository = waitSendTaskOtherPlatformsRepository;
            _sendTaskOtherPlatformsRepository = sendTaskOtherPlatformsRepository;
            _initPushDataService = initPushDataService;
            _taskRepository = taskRepository;
            _taskInfoRepository = taskInfoRepository;
            _dsOrderRepository = dsOrderRepository;
            _sendHotlineErrorDataRepository = sendHotlineErrorDataRepository;
            _fwClient = fwClient;
            _sharingConfigurationManager = sharingConfigurationManager;
            _userOperationLogRepository = userOperationLogRepository;
            _sendFwErrorDataRepository = sendFwErrorDataRepository;
            _getCaseMaterialInfoRepository = getCaseMaterialInfoRepository;
            _dsReceiveDataExchangeDataRepository = dsReceiveDataExchangeDataRepository;
            _dsReceiveEnterpriseDataRepository = dsReceiveEnterpriseDataRepository;
            _dsReceiveMediaDataRepository = dsReceiveMediaDataRepository;
            _dsReceivePoliceDataRepository = dsReceivePoliceDataRepository;
            _dsReceiveHandleDataRepository = dsReceiveHandleDataRepository;
        }
        #endregion

        #region 数据推送查询统计---最新

        #region 推送任务

        /// <summary>
        /// 省平台待推送数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getprovincewaitsendtasklist")]
        [AllowAnonymous]
        public async Task<PagedDto<DsWaitSendTaskDto>> GetProvinceWaitSendTaskList([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            #region 其他接口
            //其他接口
            var waitSendTaskProvinceOther = _waitSendTaskProvinceOtherRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                .Select(p => new DsWaitSendTaskDto
                {
                    Id = p.Id,
                    ProvinceNo = p.ProvinceNo,
                    FirstTime = p.FirstTime,
                    LastTime = p.LastTime,
                    SendTimes = p.SendTimes,
                    PlatformSource = EPlatformSource.Province,
                    TaskType = p.TaskType,
                    HttpMethod = p.HttpMethod,
                    Path = p.Path,
                    Request = p.Request,
                    PathType = p.PathType,
                    GenerationTime = p.GenerationTime,
                    CreationTime = p.CreationTime,
                    State = p.State
                })
                .MergeTable();
            #endregion

            #region 服务工单受理
            //服务工单受理
            var waitSendTaskSubmitCaseInfo = _waitSendTaskSubmitCaseInfoRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
               .Select(p => new DsWaitSendTaskDto
               {
                   Id = p.Id,
                   ProvinceNo = p.ProvinceNo,
                   FirstTime = p.FirstTime,
                   LastTime = p.LastTime,
                   SendTimes = p.SendTimes,
                   PlatformSource = EPlatformSource.Province,
                   TaskType = p.TaskType,
                   HttpMethod = p.HttpMethod,
                   Path = p.Path,
                   Request = p.Request,
                   PathType = p.PathType,
                   GenerationTime = p.GenerationTime,
                   CreationTime = p.CreationTime,
                   State = p.State
               })
                .MergeTable();
            #endregion

            #region 服务工单处理过程
            //服务工单处理过程
            var waitSendTaskSubmitCaseProcess = _waitSendTaskSubmitCaseProcessRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                 .Select(p => new DsWaitSendTaskDto
                 {
                     Id = p.Id,
                     ProvinceNo = p.ProvinceNo,
                     FirstTime = p.FirstTime,
                     LastTime = p.LastTime,
                     SendTimes = p.SendTimes,
                     PlatformSource = EPlatformSource.Province,
                     TaskType = p.TaskType,
                     HttpMethod = p.HttpMethod,
                     Path = p.Path,
                     Request = p.Request,
                     PathType = p.PathType,
                     GenerationTime = p.GenerationTime,
                     CreationTime = p.CreationTime,
                     State = p.State
                 })
                .MergeTable();
            #endregion

            #region 电话记录
            //电话记录
            var waitSendTaskSubmitCaseRecord = _waitSendTaskSubmitCaseRecordRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                 .Select(p => new DsWaitSendTaskDto
                 {
                     Id = p.Id,
                     ProvinceNo = p.ProvinceNo,
                     FirstTime = p.FirstTime,
                     LastTime = p.LastTime,
                     SendTimes = p.SendTimes,
                     PlatformSource = EPlatformSource.Province,
                     TaskType = p.TaskType,
                     HttpMethod = p.HttpMethod,
                     Path = p.Path,
                     Request = p.Request,
                     PathType = p.PathType,
                     GenerationTime = p.GenerationTime,
                     CreationTime = p.CreationTime,
                     State = p.State
                 })
                .MergeTable();
            #endregion

            #region 服务工单处理结果
            //服务工单处理结果
            var waitSendTaskSubmitCaseResult = _waitSendTaskSubmitCaseResultRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                 .Select(p => new DsWaitSendTaskDto
                 {
                     Id = p.Id,
                     ProvinceNo = p.ProvinceNo,
                     FirstTime = p.FirstTime,
                     LastTime = p.LastTime,
                     SendTimes = p.SendTimes,
                     PlatformSource = EPlatformSource.Province,
                     TaskType = p.TaskType,
                     HttpMethod = p.HttpMethod,
                     Path = p.Path,
                     Request = p.Request,
                     PathType = p.PathType,
                     GenerationTime = p.GenerationTime,
                     CreationTime = p.CreationTime,
                     State = p.State
                 })
                .MergeTable();
            #endregion

            //连表查询数据
            RefAsync<int> total = 0;
            var items = await _waitSendTaskRepository.UnionAll(waitSendTaskProvinceOther, waitSendTaskSubmitCaseInfo, waitSendTaskSubmitCaseProcess,
             waitSendTaskSubmitCaseRecord, waitSendTaskSubmitCaseResult).MergeTable()
                .OrderBy(p => p.CreationTime)
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            return new PagedDto<DsWaitSendTaskDto>(total, _mapper.Map<List<DsWaitSendTaskDto>>(items));
        }

        /// <summary>
        /// 其他平台待推送数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getotherplatformswaitsendtasklist")]
        [AllowAnonymous]
        public async Task<PagedDto<DsWaitSendTaskDto>> GetOtherPlatformsWaitSendTaskList([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            RefAsync<int> total = 0;
            //其他平台
            var items = await _waitSendTaskOtherPlatformsRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                .Select(p => new DsWaitSendTaskDto
                {
                    Id = p.Id,
                    ProvinceNo = p.ProvinceNo,
                    FirstTime = p.FirstTime,
                    LastTime = p.LastTime,
                    SendTimes = p.SendTimes,
                    PlatformSource = p.PlatformSource,
                    TaskType = p.TaskType,
                    HttpMethod = p.HttpMethod,
                    Path = p.Path,
                    Request = p.Request,
                    PathType = p.PathType,
                    GenerationTime = p.GenerationTime,
                    CreationTime = p.CreationTime
                })
                .MergeTable()
                .OrderBy(p => p.CreationTime)
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            return new PagedDto<DsWaitSendTaskDto>(total, _mapper.Map<List<DsWaitSendTaskDto>>(items));
        }

        /// <summary>
        /// 查询任务推送明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getdssenddetailtaskinfo")]
        [AllowAnonymous]
        public async Task<PagedDto<DsSendTaskDetailInfo>> GetDsSendDetailTaskInfo([FromQuery] QuerySendTaskDto dto)
        {
            //数据查询
            RefAsync<int> total = 0;
            var items = await _sendTaskDetailInfoRepository.Queryable()
                .WhereIF(dto.StartTime.HasValue, p => p.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, p => p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Id), p => p.TaskId == dto.Id)
                .WhereIF(dto.IsSuccess.HasValue, p => p.IsSuccess == dto.IsSuccess)
                .OrderByDescending(p => p.CreationTime)
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
            return new PagedDto<DsSendTaskDetailInfo>(total, items);
        }

        /// <summary>
        /// 查询已推送任务  查询类型 1：服务工单受理，2、电话记录，3、服务工单处理过程，4、服务工单处理结果，5、省平台其他推送，6、其他平台
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getdssendendtask")]
        [AllowAnonymous]
        public async Task<PagedDto<DsEndSendTaskDto>> GetDsSendEndTask([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            //数据查询
            RefAsync<int> total = 0;

            //服务工单受理
            if (dto.SelectType == "1")
            {
                //服务工单受理
                var items = await _sendTaskSubmitCaseInfoRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                   .Select(p => new DsEndSendTaskDto
                   {
                       Id = p.Id,
                       ProvinceNo = p.ProvinceNo,
                       FirstTime = p.FirstTime,
                       LastTime = p.LastTime,
                       SendTimes = p.SendTimes,
                       PlatformSource = EPlatformSource.Province,
                       TaskType = p.TaskType,
                       HttpMethod = p.HttpMethod,
                       Path = p.Path,
                       Request = p.Request,
                       PathType = p.PathType,
                       GenerationTime = p.GenerationTime,
                       CreationTime = p.CreationTime,
                       IsSuccess = p.IsSuccess,
                       ExpiredTime = p.ExpiredTime
                   })
                    .MergeTable()
                    .OrderByDescending(p => p.CreationTime)
                  .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            //电话记录
            if (dto.SelectType == "2")
            {
                //电话记录
                var items = await _sendTaskSubmitCaseRecordRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                     .Select(p => new DsEndSendTaskDto
                     {
                         Id = p.Id,
                         ProvinceNo = p.ProvinceNo,
                         FirstTime = p.FirstTime,
                         LastTime = p.LastTime,
                         SendTimes = p.SendTimes,
                         PlatformSource = EPlatformSource.Province,
                         TaskType = p.TaskType,
                         HttpMethod = p.HttpMethod,
                         Path = p.Path,
                         Request = p.Request,
                         PathType = p.PathType,
                         GenerationTime = p.GenerationTime,
                         CreationTime = p.CreationTime,
                         IsSuccess = p.IsSuccess
                     })
                    .MergeTable()
                    .OrderByDescending(p => p.CreationTime)
                    .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            //服务工单处理过程
            if (dto.SelectType == "3")
            {
                //服务工单处理过程
                var items = await _sendTaskSubmitCaseProcessRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                     .Select(p => new DsEndSendTaskDto
                     {
                         Id = p.Id,
                         ProvinceNo = p.ProvinceNo,
                         FirstTime = p.FirstTime,
                         LastTime = p.LastTime,
                         SendTimes = p.SendTimes,
                         PlatformSource = EPlatformSource.Province,
                         TaskType = p.TaskType,
                         HttpMethod = p.HttpMethod,
                         Path = p.Path,
                         Request = p.Request,
                         PathType = p.PathType,
                         GenerationTime = p.GenerationTime,
                         CreationTime = p.CreationTime,
                         IsSuccess = p.IsSuccess
                     })
                    .OrderByDescending(p => p.CreationTime)
                    .MergeTable().ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            //服务工单处理结果
            if (dto.SelectType == "4")
            {
                //服务工单处理结果
                var items = await _sendTaskSubmitCaseResultRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                     .Select(p => new DsEndSendTaskDto
                     {
                         Id = p.Id,
                         ProvinceNo = p.ProvinceNo,
                         FirstTime = p.FirstTime,
                         LastTime = p.LastTime,
                         SendTimes = p.SendTimes,
                         PlatformSource = EPlatformSource.Province,
                         TaskType = p.TaskType,
                         HttpMethod = p.HttpMethod,
                         Path = p.Path,
                         Request = p.Request,
                         PathType = p.PathType,
                         GenerationTime = p.GenerationTime,
                         CreationTime = p.CreationTime,
                         IsSuccess = p.IsSuccess,
                         FiledTime = p.FiledTime,
                         ExpiredTime = SqlFunc.Subqueryable<DsSendTaskSubmitCaseInfo>().Where(s => s.ProvinceNo == p.ProvinceNo).OrderByDesc(s => s.LastTime).Select(s => s.ExpiredTime)
                     })
                    .OrderByDescending(p => p.CreationTime)
                    .MergeTable().ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            //其他接口
            if (dto.SelectType == "5")
            {
                //其他接口
                var items = await _sendTaskProvinceOtherRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                    .Select(p => new DsEndSendTaskDto
                    {
                        Id = p.Id,
                        ProvinceNo = p.ProvinceNo,
                        FirstTime = p.FirstTime,
                        LastTime = p.LastTime,
                        SendTimes = p.SendTimes,
                        PlatformSource = EPlatformSource.Province,
                        TaskType = p.TaskType,
                        HttpMethod = p.HttpMethod,
                        Path = p.Path,
                        Request = p.Request,
                        PathType = p.PathType,
                        GenerationTime = p.GenerationTime,
                        CreationTime = p.CreationTime,
                        IsSuccess = p.IsSuccess
                    })
                    .OrderByDescending(p => p.CreationTime)
                    .MergeTable().ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            //其他平台
            if (dto.SelectType == "6")
            {
                //其他平台
                var items = await _sendTaskOtherPlatformsRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.ProvinceNo == dto.CaseSerial)
                .WhereIF(!string.IsNullOrEmpty(dto.Path), p => p.Path == dto.Path)
                    .Select(p => new DsEndSendTaskDto
                    {
                        Id = p.Id,
                        ProvinceNo = p.ProvinceNo,
                        FirstTime = p.FirstTime,
                        LastTime = p.LastTime,
                        SendTimes = p.SendTimes,
                        PlatformSource = p.PlatformSource,
                        TaskType = p.TaskType,
                        HttpMethod = p.HttpMethod,
                        Path = p.Path,
                        Request = p.Request,
                        PathType = p.PathType,
                        GenerationTime = p.GenerationTime,
                        CreationTime = p.CreationTime,
                        IsSuccess = p.IsSuccess
                    })
                    .MergeTable()
                    .OrderBy(p => p.CreationTime)
                    .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

                return new PagedDto<DsEndSendTaskDto>(total, _mapper.Map<List<DsEndSendTaskDto>>(items));
            }

            return new PagedDto<DsEndSendTaskDto>(total, null);
        }

        /// <summary>
        /// 根据ID从新推送  
        /// </summary>
        /// <param name="Id">推送任务Id</param>
        /// <param name="Type">类型 1：服务工单受理，2、电话记录，3、服务工单处理过程，4、服务工单处理结果，5、省平台其他推送，6、其他平台</param>
        /// <returns></returns>
        [HttpGet("addnewtasksendbyid")]
        [AllowAnonymous]
        public async Task AddNewTaskSendById(string Id, string Type)
        {
            if (string.IsNullOrEmpty(Id) || string.IsNullOrEmpty(Type))
                throw UserFriendlyException.SameMessage("参数无效");

            var userInfo = Request.Headers["userinfo"].ToString();
            if (string.IsNullOrEmpty(userInfo))
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            var pkcs1 = new RsaPkcs1Util(Encoding.UTF8, PublicKey, PrivateKey);
            var info = pkcs1.Decrypt(userInfo, RSAEncryptionPadding.Pkcs1);
            if (info == null)
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            var user = System.Text.Json.JsonSerializer.Deserialize<UserInfo>(info);
            if (user == null)
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            //写入操作记录

            UserOperationLog operationLog = new()
            {
                DataId = Id,
                PlatformSource = EPlatformSource.Province,
                UserId = user.UserId,
                UserName = user.UserName,
                OrgCode = user.OrgCode,
                OrgName = user.OrgName
            };
            string resultId = "";

            //服务工单受理
            if (Type == "1")
            {
                var data = await _sendTaskSubmitCaseInfoRepository.GetAsync(Id, HttpContext.RequestAborted);

                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskSubmitCaseInfo(data.OrderId, data.ProvinceNo, data.Request, data.RequestData, data.GenerationTime, HttpContext.RequestAborted);
                }
            }

            //电话记录
            if (Type == "2")
            {
                var data = await _sendTaskSubmitCaseRecordRepository.GetAsync(Id, HttpContext.RequestAborted);
                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskSubmitCaseRecord(data.ProvinceNo, data.CallId, data.CallLogId, data.Request, data.RequestData, data.GenerationTime, HttpContext.RequestAborted);
                }
            }

            //服务工单处理过程
            if (Type == "3")
            {
                var data = await _sendTaskSubmitCaseProcessRepository.GetAsync(Id, HttpContext.RequestAborted);
                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskSubmitCaseProcess(data.ProvinceNo, data.Request, data.RequestData, data.GenerationTime, HttpContext.RequestAborted);
                }
            }

            //服务工单处理结果
            if (Type == "4")
            {
                var data = await _sendTaskSubmitCaseResultRepository.GetAsync(Id, HttpContext.RequestAborted);
                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskSubmitCaseResult(data.ProvinceNo, data.Request, data.RequestData, data.GenerationTime, HttpContext.RequestAborted);
                }
            }

            //其他接口
            if (Type == "5")
            {
                var data = await _sendTaskProvinceOtherRepository.GetAsync(Id, HttpContext.RequestAborted);
                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskProvinceOther(data.TaskType, data.Path, data.ProvinceNo, data.Request, data.RequestData, data.HttpMethod, data.PathType.Value, data.FileJson, data.GenerationTime, cancellationToken: HttpContext.RequestAborted);
                }
            }

            //其他平台
            if (Type == "6")
            {
                var data = await _sendTaskOtherPlatformsRepository.GetAsync(Id, HttpContext.RequestAborted);
                if (data != null)
                {
                    operationLog.ReceiveServiceInterface = data.TaskType;
                    operationLog.Path = data.Path;
                    operationLog.RequestData = data.Request;
                    resultId = await _initPushDataService.InitDsSendTaskOtherPlatforms(data.TaskType, data.Path, data.ProvinceNo, data.Request, data.RequestData, data.PlatformSource, data.HttpMethod, data.PathType.Value, data.FileJson, data.GenerationTime, HttpContext.RequestAborted);
                }
            }

            operationLog.NewDataId = resultId;
            await _userOperationLogRepository.AddAsync(operationLog, HttpContext.RequestAborted);

            if (string.IsNullOrEmpty(resultId))
                throw UserFriendlyException.SameMessage("重新推送失败");
        }

        #endregion

        #region 及时性
        /// <summary>
        /// 查询工单及时上传列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getordertimelylist")]
        [AllowAnonymous]
        public async Task<PagedDto<object>> GetOrderTimelyList([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            //数据查询
            RefAsync<int> total = 0;

            var items = await _sendTaskSubmitCaseInfoRepository.Queryable()
                .Where(p => p.GenerationTime >= dto.StartTime && p.GenerationTime <= dto.EndTime)
               .Where(p => p.LastTime >= dto.StartTime && p.LastTime <= dto.EndTime && p.IsSuccess == ESendTaskState.PushSuccess)
                 .Select(p => new
                 {
                     index = SqlFunc.RowNumber($"{p.LastTime} asc ", $"{p.ProvinceNo}"),
                     ProvinceNo = p.ProvinceNo,
                     FirstTime = p.FirstTime,
                     LastTime = p.LastTime,
                     SendTimes = p.SendTimes,
                     StartTime = p.GenerationTime,
                     CreationTime = p.CreationTime,
                     IntervalTime = SqlFunc.DateDiff(DateType.Second, p.GenerationTime.Value, p.LastTime.Value),
                     CaseSource = p.CaseSource,
                     p.RequestData,
                     p.OrderId
                 })
                 .MergeTable()
                 .Where(d => d.index == 1)
                 .WhereIF(dto.IsSuccess.HasValue && dto.IsSuccess == true, d => SqlFunc.DateDiff(DateType.Second, d.StartTime.Value, d.LastTime.Value) <= 180)//及时上传
                 .WhereIF(dto.IsSuccess.HasValue && dto.IsSuccess == false, d => SqlFunc.DateDiff(DateType.Second, d.StartTime.Value, d.LastTime.Value) > 180)//不及时上传
                 .OrderByDescending(d => d.CreationTime)
                 .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
            return new PagedDto<object>(total, items);

        }

        /// <summary>
        /// 查询通话记录及时上传列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getcalllist")]
        [AllowAnonymous]
        public async Task<PagedDto<object>> GetCalllist([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            RefAsync<int> total = 0;
            var items = await _sendTaskSubmitCaseRecordRepository.Queryable()
                  .Where(p => p.GenerationTime >= dto.StartTime && p.GenerationTime <= dto.EndTime)
               .Where(p => p.LastTime >= dto.StartTime && p.LastTime <= dto.EndTime && p.IsSuccess == ESendTaskState.PushSuccess)
                 .Select(p => new
                 {
                     index = SqlFunc.RowNumber($"{p.LastTime} asc ", $"{p.CallLogId}"),
                     ProvinceNo = p.ProvinceNo,
                     FirstTime = p.FirstTime,
                     LastTime = p.LastTime,
                     SendTimes = p.SendTimes,
                     StartTime = p.GenerationTime,
                     CreationTime = p.CreationTime,
                     IntervalTime = SqlFunc.DateDiff(DateType.Second, p.GenerationTime.Value, p.LastTime.Value),
                     CallId = p.CallId,
                     CallLogId = p.CallLogId,
                     CallDirection = p.CallDirection,
                     OnState = p.OnState,
                     p.RequestData
                 })
                 .MergeTable()
                 .Where(d => d.index == 1)
                 .WhereIF(dto.IsSuccess.HasValue && dto.IsSuccess == true, d => SqlFunc.DateDiff(DateType.Second, d.StartTime.Value, d.LastTime.Value) <= 180)//及时上传
                 .WhereIF(dto.IsSuccess.HasValue && dto.IsSuccess == false, d => SqlFunc.DateDiff(DateType.Second, d.StartTime.Value, d.LastTime.Value) > 180)//不及时上传
                 .OrderByDescending(d => d.CreationTime)
                 .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
            return new PagedDto<object>(total, items);

        }

        /// <summary>
        /// 查询没匹配到通话记录的电话来源工单
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getordermatchingcalllist")]
        [AllowAnonymous]
        public async Task<PagedDto<object>> GetOrderMatchingCallList([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            RefAsync<int> total = 0;

            string strSql = @"SELECT ""SCO"".""ProvinceNo"",""FirstTime"",
""LastTime"",""SendTimes"", ""StartTime"",""CreationTime"",""RequestData"",""ExpiredTime"", ""CaseSource"",""OrderId""
FROM(

SELECT *
FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" DESC)  AS ""newIndex"",""ProvinceNo"",""FirstTime"",""OrderId"",
""LastTime"",""SendTimes"",""GenerationTime""AS ""StartTime"",""CreationTime"",""RequestData"",""ExpiredTime"",""RequestData""->>'CaseSource' AS ""CaseSource""

FROM ""ds_send_task_submit_case_info"" 
	where  ""GenerationTime"" >= '{0}'  AND  ""GenerationTime"" <= '{1}'  
		AND  ""LastTime"" >= '{0}'  AND  ""LastTime"" <= '{1}' AND  ""IsSuccess"" = 1 
				
) ""SCI"" WHERE ""newIndex""=1  	AND  ""CaseSource"" =  'RGDH' 
) ""SCO""
 LEFT JOIN 

(
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" DESC) as ""newIndex"",""ProvinceNo"",""CallId"",""CallLogId""
FROM
					""ds_send_task_submit_case_record"" 
				WHERE
				 ""GenerationTime"" >= '{0}'  AND  ""GenerationTime"" <= '{1}' 
					AND  ""LastTime"" >= '{0}'  AND  ""LastTime"" <= '{1}'  AND  ""IsSuccess"" = 1
    ) ""SCR"" WHERE ""newIndex""=1
)""SCRC""
ON ""SCO"".""ProvinceNo""=""SCRC"".""ProvinceNo""
WHERE  ""SCRC"".""ProvinceNo""  IS NULL  ";
            strSql = string.Format(strSql, dto.StartTime, dto.EndTime);
            return await _waitSendTaskRepository.GetDataBySql(strSql, dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

           
        }

        /// <summary>
        /// 及时率查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getcalculateuploadrate")]
        [AllowAnonymous]
        public async Task<CalculateUploadRateDto> GetCalculateUploadRate([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.EndTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));
            CalculateUploadRateDto calculateUploadRateDto = new();

            //工单
            var orderRate = await _dsOrderSendRepository.Queryable()
                   .LeftJoin<DsTelCall>((p, o) => p.CallId == o.CallId)
                    .Where((p, o) => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime && p.FirstSendProvinceTime >= dto.StartTime && p.FirstSendProvinceTime <= dto.EndTime)
                    .Where((p, o) => p.FirstSendProvinceTime.HasValue && p.StartTime.HasValue)
                    .Select((p, o) => new
                    {
                        p.OrderId,
                        p.StartTime,
                        p.FirstSendProvinceTime,
                        p.CaseSourceCode,
                        o.CallId,
                        p.HandleState,
                        p.ExpiredTime,
                        p.ActualHandleTime
                    }).MergeTable()
                    .Select(p => new CalculateUploadRateDto
                    {
                        OrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.OrderId != null, 1, 0)),//总工单数
                        OrderTimely = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Second, p.StartTime.Value, p.FirstSendProvinceTime.Value) <= 180, 1, 0)),//及时
                        OrderNotTimely = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Second, p.StartTime.Value, p.FirstSendProvinceTime.Value) > 180, 1, 0)),//为及时

                        RGDHOrderCount = SqlFunc.AggregateSum(SqlFunc.IIF(p.CaseSourceCode == "RGDH", 1, 0)),
                        ContainTel = SqlFunc.AggregateSum(SqlFunc.IIF(p.CaseSourceCode == "RGDH" && p.CallId != null, 1, 0)),//匹配数
                        NotContainTel = SqlFunc.AggregateSum(SqlFunc.IIF(p.CaseSourceCode == "RGDH" && p.CallId == null, 1, 0)),//未匹配数

                        HandleEndOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.HandleState == "办理完成", 1, 0)),//办结件数
                        HandleEndOrderOnTime = SqlFunc.AggregateSum(SqlFunc.IIF(p.HandleState == "办理完成" && p.ExpiredTime > p.ActualHandleTime, 1, 0)),//按时办结件数
                    })
                    .FirstAsync();

            //电话
            var callRate = await _dsTelCallRepository.Queryable()
              .Where(p => p.OverTime >= dto.StartTime && p.OverTime <= dto.EndTime && p.FirstSendProvinceTime >= dto.StartTime && p.FirstSendProvinceTime <= dto.EndTime)
              .Where(p => p.FirstSendProvinceTime.HasValue && p.OverTime.HasValue)
              .Select(p => new
              {
                  p.CallId,
                  p.OverTime,
                  p.FirstSendProvinceTime,
                  p.OnState
              })
              .MergeTable()
               .Select(p => new CalculateUploadRateDto
               {
                   TelCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.CallId != null, 1, 0)),//总电话数
                   TelTimely = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Second, p.OverTime.Value, p.FirstSendProvinceTime.Value) <= 180, 1, 0)),//及时
                   TelNotTimely = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Second, p.OverTime.Value, p.FirstSendProvinceTime.Value) > 180, 1, 0)),//为及时
                   TelConnectCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState == "On", 1, 0)),//接通数量
                   TelNotConnectCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.OnState != "On", 1, 0)),//未接通数量

               })
               .FirstAsync();

            //回访
            var visitList = await _dsOrderVisitSendRepository.Queryable()
                .Where(p => p.VisitTime >= dto.StartTime && p.VisitTime <= dto.EndTime && p.IsProvince == false && p.FirstSendProvinceTime >= dto.StartTime && p.FirstSendProvinceTime <= dto.EndTime)
                .Select(p => new
                {
                    p.SubjectResultSatifyCode,
                    p.VisitTime,
                    p.IsProvince,
                    p.Source,
                    p.ProvinceNo
                })
                .MergeTable()
                .Select(p => new CalculateUploadRateDto
                {
                    VisitCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.SubjectResultSatifyCode != null, 1, 0)),//回访总量
                    SatisfactionCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.SubjectResultSatifyCode != null && p.SubjectResultSatifyCode != "2", 1, 0)),//满意数量
                    NotSatisfiedCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.SubjectResultSatifyCode != null && p.SubjectResultSatifyCode == "2", 1, 0)),//不满意数量
                })
                .FirstAsync();

            //查询知识总量
            calculateUploadRateDto.KnowledgeCount = await _knowledgeRawDataRepository.Queryable()
                       .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                       .CountAsync();

            //修改知识库量-以第一次上传成功的记录为准
            calculateUploadRateDto.UpdateKnowledgeCount = await _waitSendTaskProvinceOtherRepository.Queryable()
                .Where(p => p.FirstTime >= dto.StartTime && p.FirstTime <= dto.EndTime && p.TaskType == "GetKnowledgeInfoUpdate")
                .Select(p => new
                {
                    p.Id,
                    RowID = SqlFunc.RowNumber($"{p.CreationTime} asc ")
                })
                .MergeTable()
                .Where(p => p.RowID == 1)
                .CountAsync();

            //工单
            if (orderRate != null)
            {
                calculateUploadRateDto.OrderCountNum = orderRate.OrderCountNum;
                calculateUploadRateDto.OrderTimely = orderRate.OrderTimely;
                calculateUploadRateDto.OrderNotTimely = orderRate.OrderNotTimely;
                calculateUploadRateDto.RGDHOrderCount = orderRate.RGDHOrderCount;
                calculateUploadRateDto.ContainTel = orderRate.ContainTel;
                calculateUploadRateDto.NotContainTel = orderRate.NotContainTel;
                calculateUploadRateDto.HandleEndOrderNum = orderRate.HandleEndOrderNum;
                calculateUploadRateDto.HandleEndOrderOnTime = orderRate.HandleEndOrderOnTime;
            }

            //电话
            if (callRate != null)
            {
                calculateUploadRateDto.TelCountNum = callRate.TelCountNum;
                calculateUploadRateDto.TelTimely = callRate.TelTimely;
                calculateUploadRateDto.TelNotTimely = callRate.TelNotTimely;
                calculateUploadRateDto.TelConnectCountNum = callRate.TelConnectCountNum;
                calculateUploadRateDto.TelNotConnectCountNum = callRate.TelNotConnectCountNum;
            }

            //回访
            if (visitList != null)
            {
                calculateUploadRateDto.VisitCountNum = visitList.VisitCountNum;
                calculateUploadRateDto.SatisfactionCountNum = visitList.SatisfactionCountNum;
                calculateUploadRateDto.NotSatisfiedCountNum = visitList.NotSatisfiedCountNum;
            }
            return calculateUploadRateDto;
        }

        #endregion

        #endregion

        #region 系统配置

        /// <summary>
        /// 上传数据系统配置-查询详情
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("info-configurationinformation")]
        public async Task<ConfigurationInformation> GetStandard()
        {
            var sandard = await _configurationInformationRepository.Queryable().FirstAsync();
            if (sandard == null)
            {
                ConfigurationInformation configuration = new()
                {
                    CrntSeatNum = 0,
                    CrntTelNum = 0,
                    SeatNum = 0,
                    SeatHwyNum = 0,
                    MissedCallCount = 0
                };
                var Id = await _configurationInformationRepository.AddAsync(configuration, HttpContext.RequestAborted);
                return await _configurationInformationRepository.GetAsync(Id, HttpContext.RequestAborted);
            }
            return sandard;
        }

        /// <summary>
        /// 上传数据系统配置-修改
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPut("update-configurationinformation")]
        public async Task UpdateStandard([FromBody] ConfigurationInformationDto dto)
        {
            var standard = await _configurationInformationRepository.GetAsync(dto.Id);
            if (standard != null)
            {
                _mapper.Map(dto, standard);
                await _configurationInformationRepository.UpdateAsync(standard, HttpContext.RequestAborted);
                await _configurationInformationCache.GetOrSetAsync(ConstantSettings.ConfigurationInformationCacheKey, dto, cancellationToken: HttpContext.RequestAborted);
            }
        }
        #endregion

        #region 对接平台用户管理
        /// <summary>
        /// 对接平台用户管理
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("adduserinfo")]
        [AllowAnonymous]
        public async Task AddUserInfo([FromBody] AddUserInfoDto dto)
        {
            var tokenInfo = _mapper.Map<DsUserTokenInfo>(dto);
            if (string.IsNullOrEmpty(tokenInfo.AuthIP))
                tokenInfo.AuthIP = "*";
            await _dsUserTokenInfoRepository.AddAsync(tokenInfo, HttpContext.RequestAborted);
        }

        /// <summary>
        /// 查询对接平台用户信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("get-user-info-list")]
        [AllowAnonymous]
        public async Task<PagedDto<DsUserTokenInfo>> GetUserInfoList([FromQuery] PagedKeywordRequest dto)
        {
            RefAsync<int> total = 0;
            var items = await _dsUserTokenInfoRepository.Queryable()
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), p => p.PlatformSourceName.Contains(dto.Keyword))
                  .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            return new PagedDto<DsUserTokenInfo>(total, items);
        }
        #endregion

        #region 推送业务系统失败

        /// <summary>
        /// 查询推送业务系统失败数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>  
        [HttpGet("getsendhotlineerrordata")]
        [AllowAnonymous]
        public async Task<PagedDto<SendHotlineErrorData>> GetSendHotlineErrorData([FromQuery] QuerySendTaskDto dto)
        {
            if (!dto.StartTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
            if (!dto.EndTime.HasValue)
                dto.StartTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));

            //数据查询
            RefAsync<int> total = 0;
            var items = await _sendHotlineErrorDataRepository.Queryable()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .OrderBy(p => p.CreationTime)
                 .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            return new PagedDto<SendHotlineErrorData>(total, items);
        }

        /// <summary>
        /// 从新推送
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        [HttpGet("pushfromhotlinedata")]
        [AllowAnonymous]
        public async Task PushFromHotlineData(string Id)
        {
            var data = await _sendHotlineErrorDataRepository.GetAsync(p => p.Id == Id, HttpContext.RequestAborted);
            if (data == null)
                throw UserFriendlyException.SameMessage("重新推送失败");

            var userInfo = Request.Headers["userinfo"].ToString();
            if (string.IsNullOrEmpty(userInfo))
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            var pkcs1 = new RsaPkcs1Util(Encoding.UTF8, PublicKey, PrivateKey);
            var info = pkcs1.Decrypt(userInfo, RSAEncryptionPadding.Pkcs1);
            if (info == null)
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            var user = System.Text.Json.JsonSerializer.Deserialize<UserInfo>(info);
            if (user == null)
                throw UserFriendlyException.SameMessage("用户信息获取失败");

            bool isSuccess = true;
            string error = "";
            //调用工单创建接口
            try
            {
                //推业务系统
                if (data.ReceiveServiceInterface == "rest/receive_case_info")
                {
                    var result = await _fwClient.RequestNoTokenAsync<HotlineClientResponse>(data.Path, data.HttpMethod, data.RequestData);
                    if (result == null || result.code != 0)
                    {
                        isSuccess = false;
                        error = result?.message;
                    }
                }
                //推旧系统
                if (data.ReceiveServiceInterface == "SendOldHotlineData")
                {
                    ConfigurationSendDataOld configSendDataOld = new();
                    if (_sharingConfigurationManager.IsCity(ConfigurationConsts.YiBin))
                        configSendDataOld = _sharingConfigurationManager.GetYiBinConfig().SendDataOld;
                    else if (_sharingConfigurationManager.IsCity(ConfigurationConsts.ZiGong))
                        configSendDataOld = _sharingConfigurationManager.GetZiGongConfig().SendDataOld;
                    else if (_sharingConfigurationManager.IsCity(ConfigurationConsts.LuZhou))
                        configSendDataOld = _sharingConfigurationManager.GetLuZhouConfig().SendDataOld;

                    //var configSendDataOld = _sharingConfigurationManager.GetYiBinConfig().SendDataOld;
                    //先验证是否开启推送
                    if (configSendDataOld == null || !configSendDataOld.IsSend)
                        return;

                    var url = configSendDataOld.AddressUrl + data.Path;
                    var response = await _fwClient.RequestAsync<FwResponse>(url, data.HttpMethod, data.RequestData, HttpContext.RequestAborted);
                    if (response == null && response.code != 1)
                    {
                        isSuccess = false;
                        error = response?.msg;
                    }
                }
            }
            catch (Exception ex)
            {
                isSuccess = false;
                error = ex.Message;
            }

            //写入操作记录

            UserOperationLog operationLog = new()
            {
                DataId = data.Id,
                NewDataId = data.Id,
                PlatformSource = EPlatformSource.Hotline,
                ReceiveServiceInterface = data.ReceiveServiceInterface,
                Path = data.Path,
                RequestData = data.RequestData,
                UserId = user.UserId,
                UserName = user.UserName,
                OrgCode = user.OrgCode,
                OrgName = user.OrgName
            };

            await _userOperationLogRepository.AddAsync(operationLog, HttpContext.RequestAborted);

            //推送失败添加
            if (!isSuccess)
            {
                data.SendTimes = data.SendTimes + 1;
                data.ErrorMessage = error;
                await _sendHotlineErrorDataRepository.UpdateAsync(data, HttpContext.RequestAborted);
            }
            else
            {
                await _sendHotlineErrorDataRepository.RemoveAsync(data, cancellationToken: HttpContext.RequestAborted);
                await _sendFwErrorDataRepository.RemoveAsync(p => p.Id == data.DataId, cancellationToken: HttpContext.RequestAborted);
            }
        }

        #endregion

        #region 省查询
        /// <summary>
        /// 查询附件信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getcasematerialinfolist")]
        [AllowAnonymous]
        public async Task<PagedDto<DsGetCaseMaterialInfo>> GetCaseMaterialInfoList([FromQuery] QueryCaseMaterialInfoDto dto)
        {
            RefAsync<int> total = 0;
            var items = await _getCaseMaterialInfoRepository.Queryable()
                .WhereIF(dto.StartTime.HasValue, p => p.CreationTime >= dto.StartTime.Value)
                .WhereIF(dto.EndTime.HasValue, p => p.CreationTime >= dto.EndTime.Value)
                .WhereIF(!string.IsNullOrEmpty(dto.CaseSerial), p => p.CaseSerial == dto.CaseSerial)
                .OrderByDescending(p => p.CreationTime)
                 .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
            return new PagedDto<DsGetCaseMaterialInfo>(total, items);
        }

        /// <summary>
        /// 修改工单来源推送省平台
        /// </summary>
        /// <returns></returns>
        [HttpGet("updateordersource")]
        [AllowAnonymous]
        public async Task UpdateOrderSourceAsync()
        {
            //查询数据
            var items = await _dsOrderSendRepository.Queryable()
                .LeftJoin<DsTelCall>((p, o) => p.CallId == o.CallId)
                .Where((p, o) => p.FirstSendProvinceTime >= Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"))
                && p.FirstSendProvinceTime <= Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59")) && p.CaseSourceCode == "RGDH")
                .Where((p, o) => p.FirstSendProvinceTime.HasValue && p.HandleState == "办理完成")
                .Where((p, o) => o.CallId == null || o.CallId == "")
                .OrderByDescending((p, o) => p.CreationTime)
                .ToListAsync();

            //处理数据
            if (items != null && items.Any())
            {
                // _logger.LogWarning("读取到条数：" + "--------------" + items.Count);
                List<string> Ids = [];
                foreach (var item in items)
                {
                    Ids.Add(item.OrderId);
                }
                //推送数据
                if (Ids != null && Ids.Count > 0)
                {
                    UpdateOrderSourceChannelDto data = new()
                    {
                        Ids = Ids,
                        IsPush = true
                    };
                    await _fwClient.RequestNoTokenAsync<HotlineClientResponse>("api/v1/Order/update-order-sourcechannel", "Post", System.Text.Json.JsonSerializer.Serialize(data));
                }
            }
        }
        #endregion

        /// <summary>
        /// 查询接收到的原始数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("get_receive_raw_data")]
        [AllowAnonymous]
        public async Task<PagedDto<DsReceiveHandleData>> GetReceiveRawData([FromQuery] ReportPagedRequest dto)
        {
            RefAsync<int> total = 0;
            var items = await _dsReceiveHandleDataRepository.Queryable()
                    .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                    .WhereIF(!string.IsNullOrEmpty(dto.Keyword), p => p.OrderNo.Contains(dto.Keyword))
                    .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
            if (items != null)
            {
                foreach (var item in items)
                {
                    item.ReceiveData = System.Text.RegularExpressions.Regex.Unescape(item.ReceiveData);
                }
            }
            return new PagedDto<DsReceiveHandleData>(total, items);
        }

        /// <summary>
        /// 数据汇聚情况
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("get_data_aggregation_situation")]
        [AllowAnonymous]
        public List<DataAggregationSituationDto> GetDataAggregationSituation([FromQuery] ReportPagedRequest dto)
        {

            string strsql = @"
/*临时表对象*/

/*呼入记录 - 以最后同步成功时间为准*/

CREATE TEMPORARY TABLE ""CASE_RECORD_LAST"" (
    ""OnState"" varchar(16),""CallLogId""  varchar(50)
) ON COMMIT PRESERVE ROWS ;

/*呼入总量、呼入接通量*/ 

INSERT INTO ""CASE_RECORD_LAST""(""OnState"",""CallLogId"")

SELECT ""OnState"",""CallLogId""

FROM 

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""CallLogId"" ORDER BY ""LastTime"" DESC) as ""newIndex"",""Id"",""OnState"",""CallLogId"" 

FROM ""ds_send_task_submit_case_record"" 

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""GenerationTime"">='{0}' AND ""GenerationTime""<='{1}' AND

 ""IsSuccess""=1 AND ""CallDirection""='0'

) SCR WHERE ""newIndex""=1 ;


/*呼入记录 - 以第一次同步成功时间为准*/
CREATE TEMPORARY TABLE ""CASE_RECORD_FIRST"" (
    ""CallLogId""  varchar(50) ,""GenerationTime"" timestamp,""LastTime"" timestamp
) ON COMMIT PRESERVE ROWS ;

/*呼入上传话务数据总量、呼入及时上传话务数据量*/ 

INSERT INTO ""CASE_RECORD_FIRST""(""CallLogId"",""GenerationTime"",""LastTime"")

SELECT ""CallLogId"",""GenerationTime"",""LastTime""

FROM 

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""CallLogId"" ORDER BY ""LastTime"" asc) as ""newIndex"",""GenerationTime"",""LastTime"",""CallLogId""

FROM ""ds_send_task_submit_case_record"" 

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""GenerationTime"">='{0}' AND ""GenerationTime""<='{1}' AND

 ""IsSuccess""=1 AND ""CallDirection""='0'

) SCR WHERE ""newIndex""=1 ;





/*服务工单受理记录 - 以第一次同步成功时间为准*/ 

CREATE TEMPORARY TABLE ""CASE_INFO_FIRST"" (
    ""ProvinceNo""  varchar(50) ,""GenerationTime"" timestamp,""LastTime"" timestamp,""CaseSource"" varchar(20)
)ON COMMIT PRESERVE ROWS ;

/*上传工单数据总量、及时上传工单数据总量*/

INSERT INTO ""CASE_INFO_FIRST""(""ProvinceNo"",""GenerationTime"",""LastTime"",""CaseSource"")

SELECT ""ProvinceNo"",""GenerationTime"",""LastTime"",""CaseSource""

FROM 

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""LastTime"" asc) as ""newIndex"",""Id"",""GenerationTime"",""LastTime"",""ProvinceNo"", ""CaseSource""

FROM ""ds_send_task_submit_case_info""

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""GenerationTime"">='{0}' AND ""GenerationTime""<='{1}' AND

 ""IsSuccess""=1 

) SCR WHERE ""newIndex""=1 ;





/*服务工单受理记录 - 以最后一次同步成功时间为准*/ 

CREATE TEMPORARY TABLE ""CASE_INFO_LAST"" (
    ""CaseSource"" varchar(50),
		""ProvinceNo"" varchar(50) ,
		""TfdBackTimeBf"" timestamp,
		""CaseProcessType"" varchar(20),
		""LastTime"" timestamp
)ON COMMIT PRESERVE ROWS ;

/*电话工单量、与电话工单匹配的话务数据量*/

INSERT INTO ""CASE_INFO_LAST""(""CaseSource"",""ProvinceNo"",""TfdBackTimeBf"",""CaseProcessType"",""LastTime"")

select ""CaseSource"",""ProvinceNo"",(SELECT ""TfdBackTimeBf"" ::timestamp) AS ""TfdBackTimeBf"",""CaseProcessType"",""LastTime"" 

from 

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""LastTime"" DESC) as ""newIndex"",""Id"",""CaseSource"",""ProvinceNo"",
 ""RequestData""->> 'TfdBackTimeBf' AS  ""TfdBackTimeBf""
,""RequestData""->> 'CaseProcessType' ""CaseProcessType"",""LastTime""

FROM ""ds_send_task_submit_case_info""

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""GenerationTime"">='{0}' AND ""GenerationTime""<='{1}' AND

 ""IsSuccess""=1 

) SCR WHERE ""newIndex""=1 ;



/*服务工单结果记录 - 以第一次同步成功时间为准*/

CREATE TEMPORARY TABLE ""CASE_RESULT_FIRST"" (
		""ProvinceNo"" varchar(50) ,
		""FiledTime"" timestamp
)ON COMMIT PRESERVE ROWS ;
INSERT INTO ""CASE_RESULT_FIRST""(""ProvinceNo"",""FiledTime"")

SELECT ""ProvinceNo"",(SELECT ""FiledTime"" ::timestamp) AS""FiledTime"" 

FROM

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""LastTime"" ASC) as ""newIndex"",""Id"",""ProvinceNo"",""RequestData""->> 'FinishTime' AS""FiledTime""

FROM ""ds_send_task_submit_case_result""

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""FiledTime"">='{0}' AND ""FiledTime""<='{1}' AND

 ""IsSuccess""=1 

) SCR WHERE ""newIndex""=1 ;


/*服务工单结果记录 - 以最后一次同步成功时间为准*/

CREATE TEMPORARY TABLE ""CASE_RESULT_LAST"" (
		""ProvinceNo"" varchar(50) ,
		""FiledTime"" timestamp
)ON COMMIT PRESERVE ROWS ;
INSERT INTO ""CASE_RESULT_LAST""(""ProvinceNo"",""FiledTime"")

SELECT ""ProvinceNo"",(SELECT ""FiledTime"" ::timestamp) AS ""FiledTime"" 

FROM

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""LastTime"" ASC) as ""newIndex"",""Id"",""ProvinceNo"",""RequestData""->> 'FinishTime' AS ""FiledTime""

FROM ""ds_send_task_submit_case_result""

WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""FiledTime"">='{0}' AND ""FiledTime""<='{1}' AND

 ""IsSuccess""=1 

) SCR WHERE ""newIndex""=1 ;


/*结转工单记录*/
CREATE TEMPORARY TABLE ""CASE_RELAY"" (
		""ProvinceNo"" varchar(50) ,
		""TfdBackTimeBf"" timestamp
)ON COMMIT PRESERVE ROWS ;
INSERT INTO ""CASE_RELAY""(""ProvinceNo"",""TfdBackTimeBf"")

SELECT ""ProvinceNo"",(SELECT ""TfdBackTimeBf"" ::timestamp) AS""TfdBackTimeBf"" 

FROM 

(

SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" DESC) as ""newIndex"",""Id"",""ProvinceNo"",
""RequestData""->> 'TfdBackTimeBf' AS ""TfdBackTimeBf"", ""RequestData""->> 'CaseProcessType' ""CaseProcessType""

FROM ""ds_send_task_submit_case_info""

WHERE  ""GenerationTime"">='{2}' AND ""GenerationTime""<='{0}' AND ""IsSuccess""=1 

) SCR WHERE ""newIndex""=1 ;


select 
CASE WHEN ""呼入总量"">0 THEN CAST(CAST(((""呼入接通量""*1.0)/""呼入总量"") as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""CalConnectionRate"" ,

CASE WHEN ""电话工单量"">0 THEN CAST(CAST(((""与电话工单匹配的话务数据量""*1.0)/""电话工单量"") as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""MatchingRateOfCallData"" ,

CASE WHEN ""呼入上传话务数据总量"">0 THEN CAST(CAST(((""呼入及时上传话务数据量""*1.0)/""呼入上传话务数据总量"") as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""TimelyUploadRateOfCallData"" ,

CASE WHEN ""上传工单数据总量"">0 THEN CAST(CAST(((""及时上传工单数据总量""*1.0)/""上传工单数据总量"") as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""TimelyUploadRateOfOrderData"" ,

(""上传工单数据总量""+""结转已办件量""+""结转待办件量"") AS ""AcceptanceQuantity"",

""办结件数""AS ""NumbeOfCompletedCases"",

CASE WHEN (""时间范围按时办结量""+""结转按时已办结量""+""时间范围超期办结量""+""结转超期已办结量""+""时间范围超期未办量""+""结转超期未办量"")>0 

THEN CAST(CAST((((""时间范围按时办结量""+""结转按时已办结量"")*1.0)/(""时间范围按时办结量""+""结转按时已办结量""+""时间范围超期办结量""+""结转超期已办结量""+""时间范围超期未办量""+""结转超期未办量"")) as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""OnTimeCompletionRate"" ,

""回访量"" AS ""FollowUpVolume"",

""不满意件"" AS ""DissatisfiedItems"",

CASE WHEN ""回访量"">0 THEN CAST(CAST(((""不满意件""*1.0)/""回访量"") as decimal(10,5))*100 as decimal(10,3)) ELSE 0 END AS ""DissatisfactionRate"" ,

""知识库总量"" AS ""KnowledgeTotalNum"",

""更新知识量"" AS ""KnowledgeUpdateTotalNum"",

""省交办单超期量"" as ""OverdueDeliveryOrder"" 

 FROM (
SELECT 


(
SELECT COUNT(0) ""呼入总量"" FROM ""CASE_RECORD_LAST"" 
) ""呼入总量"",

(
SELECT COUNT(0) ""呼入接通量"" FROM ""CASE_RECORD_LAST"" WHERE ""OnState""='1'
) ""呼入接通量"",

(
SELECT COUNT(0) ""呼入上传话务数据总量"" FROM ""CASE_RECORD_FIRST"" 
) ""呼入上传话务数据总量"",

(
SELECT COUNT(0) ""呼入及时上传话务数据量"" FROM ""CASE_RECORD_FIRST"" WHERE  EXTRACT(EPOCH FROM (""GenerationTime"" - ""LastTime""))  < 180 
) ""呼入及时上传话务数据量"",

(
SELECT COUNT(0) ""上传工单数据总量"" FROM ""CASE_INFO_FIRST"" 
) ""上传工单数据总量"",

(
SELECT COUNT(0) ""及时上传工单数据总量"" FROM ""CASE_INFO_FIRST"" WHERE  EXTRACT(EPOCH FROM (""GenerationTime"" - ""LastTime""))  < 180 
) ""及时上传工单数据总量"",

(
select COUNT(0) ""电话工单量"" from ""CASE_INFO_LAST"" where ""CaseSource""='RGDH' 
) ""电话工单量"",

(
SELECT COUNT(0) ""与电话工单匹配的话务数据量"" FROM 
(
select ""ProvinceNo"" from ""CASE_INFO_LAST"" where ""CaseSource""='RGDH' 
) ""SCI"" INNER JOIN
(
SELECT ""Id"",""ProvinceNo"" FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY ""CallId"",""ProvinceNo"" ORDER BY ""CreationTime"" DESC) as ""newIndex"",""Id"",""ProvinceNo""
FROM ""ds_send_task_submit_case_record""
WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""GenerationTime"">='{0}' AND ""GenerationTime""<='{1}' AND ""IsSuccess""=1 
        ) SCR WHERE ""newIndex""=1
) ""SCR"" ON ""SCI"".""ProvinceNo"" = ""SCR"".""ProvinceNo"" 
) ""与电话工单匹配的话务数据量"",


(
SELECT COUNT(0) ""结转已办件量"" FROM 
(
SELECT ""ProvinceNo"" from ""CASE_RESULT_FIRST""
)""SCR"" 
INNER JOIN ""CASE_RELAY"" ""SCI"" ON ""SCR"".""ProvinceNo"" = ""SCI"".""ProvinceNo"" 
) ""结转已办件量"",

(
SELECT COUNT(0) ""结转待办件量"" FROM ""CASE_RELAY"" ""CR""
WHERE NOT EXISTS(SELECT ""Id"" FROM ""ds_send_task_submit_case_result"" WHERE ""ProvinceNo"" = ""CR"".""ProvinceNo"" AND ""IsSuccess""=1)
) ""结转待办件量"",

(
SELECT COUNT(0) ""办结件数"" FROM ""CASE_RESULT_FIRST""  ""CRF""
WHERE EXISTS (SELECT ""Id"" FROM ""ds_send_task_submit_case_info"" WHERE ""ProvinceNo"" = ""CRF"".""ProvinceNo"" AND ""IsSuccess""=1)
) ""办结件数"",

(

SELECT COUNT(0) ""时间范围按时办结量""  FROM ""CASE_INFO_LAST"" ""SCI"" 
INNER JOIN ""CASE_RESULT_LAST"" ""SCR"" ON ""SCI"".""ProvinceNo"" = ""SCR"".""ProvinceNo"" 
WHERE ""TfdBackTimeBf"" >= ""FiledTime"" 
) ""时间范围按时办结量"",

(
SELECT COUNT(0) ""结转按时已办结量"" FROM ""CASE_RESULT_FIRST"" ""SCR"" 
INNER JOIN ""CASE_RELAY"" ""SCI"" ON ""SCR"".""ProvinceNo"" = ""SCI"".""ProvinceNo"" 
WHERE ""TfdBackTimeBf"" >= ""FiledTime"" 
) ""结转按时已办结量"",

(
SELECT COUNT(0) ""时间范围超期办结量"" FROM ""CASE_INFO_LAST"" ""SCI"" 
INNER JOIN ""CASE_RESULT_LAST"" ""SCR"" ON ""SCI"".""ProvinceNo"" = ""SCR"".""ProvinceNo"" 
WHERE ""FiledTime"" > ""TfdBackTimeBf"" 
) ""时间范围超期办结量"",



(
SELECT COUNT(0) ""结转超期已办结量"" FROM ""CASE_RESULT_FIRST"" ""SCR"" 
INNER JOIN ""CASE_RELAY"" ""SCI"" ON ""SCR"".""ProvinceNo"" = ""SCI"".""ProvinceNo"" 
WHERE ""FiledTime"" > ""TfdBackTimeBf"" 
) ""结转超期已办结量"",

(
SELECT COUNT(0) ""时间范围超期未办量"" FROM ""CASE_INFO_LAST"" ""CIL""
WHERE '{1}'  > ""TfdBackTimeBf"" AND
 NOT EXISTS(SELECT ""Id"" FROM ""ds_send_task_submit_case_result"" WHERE ""CIL"".""ProvinceNo"" = ""ProvinceNo"" AND ""LastTime""<='{1}'   AND ""IsSuccess""=1)
) ""时间范围超期未办量"",

(
SELECT COUNT(0) ""结转超期未办量"" FROM ""CASE_RELAY""  ""CR"" 
WHERE NOT EXISTS(SELECT ""Id"" FROM ""ds_send_task_submit_case_result"" WHERE ""CR"".""ProvinceNo"" = ""ProvinceNo""  AND ""IsSuccess""=1) AND now() > ""TfdBackTimeBf"" 
) ""结转超期未办量"",


(
SELECT COUNT(0) ""回访量"" FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" ASC) as ""newIndex"",""Id"",""ProvinceNo"" FROM ""ds_send_task_province_other""
WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""VisitTime""='{0}' AND ""VisitTime""<='{1}' AND  ""IsSuccess""=1 
) ""SVI"" WHERE ""newIndex""=1 
AND EXISTS (SELECT ""Id"" FROM ""ds_send_task_submit_case_info"" 
WHERE ""ProvinceNo"" = ""ProvinceNo"" AND ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""IsSuccess""=1 )
) ""回访量"",

(
SELECT COUNT(0) ""不满意件""FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" DESC) as ""newIndex"",""Id"",""ProvinceNo"",""SubjectResultSatify"" FROM ""ds_send_task_province_other""
WHERE ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""VisitTime"">='{0}' AND ""VisitTime""<='{1}' AND ""IsSuccess""=1 
) ""SVI"" 
WHERE ""newIndex""=1 AND (""SubjectResultSatify""='1' OR ""SubjectResultSatify""='2') AND EXISTS (
SELECT ""Id"" FROM ""ds_send_task_submit_case_info""
WHERE ""ProvinceNo"" = ""ProvinceNo"" AND ""LastTime"">='{0}' AND ""LastTime""<='{1}' AND ""IsSuccess""=1 )
) ""不满意件"",

(
SELECT COUNT(0) ""知识库总量"" FROM ""ds_send_task_province_other"" WHERE ""CreationTime"" <= '{1}' and ""IsSuccess""=1 and ""Path""='get_knowledge_info_send'
) ""知识库总量"",

(
SELECT COUNT(0) ""更新知识量"" FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY ""ProvinceNo"" ORDER BY ""CreationTime"" ASC) as ""newIndex"",""Id"",""ProvinceNo""
FROM ""ds_send_task_province_other"" 
WHERE ""CreationTime"">='{0}' AND ""CreationTime""<='{1}' and ""IsSuccess""=1 and ""Path""='get_knowledge_info_update'
) ""GKIU"" WHERE ""newIndex""=1 
) ""更新知识量"",

(
0
) ""省交办单超期量""

)""_data""


";

            strsql = string.Format(strsql, dto.StartTime, dto.EndTime, dto.StartTime.Value.AddMonths(-3));

            return _waitSendTaskRepository.GetDataAggregationSituation(strsql);

        }


    }
}