hotline/src/Hotline.Application/StatisticalReport/OrderReportApplication.cs

using Hotline.Application.Orders;
using Hotline.Caching.Interfaces;
using Hotline.Caching.Services;
using Hotline.CallCenter.Calls;
using Hotline.FlowEngine.WorkflowModules;
using Hotline.FlowEngine.Workflows;
using Hotline.Identity.Accounts;
using Hotline.Orders;
using Hotline.Settings;
using Hotline.Settings.TimeLimits;
using Hotline.Share.Dtos.Bi;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using Hotline.Users;
using JiebaNet.Segmenter.Common;
using MapsterMapper;
using MediatR;
using Microsoft.IdentityModel.Tokens;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System.Data;
using XF.Domain.Authentications;
using XF.Domain.Dependency;
using XF.Domain.Exceptions;
using XF.Domain.Repository;

namespace Hotline.Application.StatisticalReport
{
    public class OrderReportApplication : IOrderReportApplication, IScopeDependency
    {
        private readonly IOrderRepository _orderRepository;
        private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<OrderDelay> _orderDelayRepository;
        private readonly IMapper _mapper;
        private readonly IRepository<OrderPublish> _orderPublishRepository;
        private readonly ISessionContext _sessionContext;
        private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
        private readonly IRepository<OrderScreen> _orderScreenRepository;
        private readonly IOrderSecondaryHandlingApplication _orderSecondaryHandlingApplication;
        private readonly ITimeLimitDomainService _timeLimitDomainService;
        private readonly IRepository<SystemDicData> _systemDicDataRepository;
        private readonly IRepository<TrCallRecord> _trCallRecordRepository;
        private readonly ISystemSettingCacheManager _systemSettingCacheManager;
        private readonly IRepository<User> _userRepository;
        private readonly IRepository<Account> _accountRepository;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="orderRepository"></param>
        /// <param name="orderVisitDetailRepository"></param>
        /// <param name="orderDelayRepository"></param>
        /// <param name="mapper"></param>
        /// <param name="orderPublishRepository"></param>
        /// <param name="sessionContext"></param>
        /// <param name="workflowTraceRepository"></param>
        /// <param name="orderScreenRepository"></param>
        /// <param name="orderSecondaryHandlingApplication"></param>
        /// <param name="timeLimitDomainService"></param>
        public OrderReportApplication(
            IOrderRepository orderRepository,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            IRepository<OrderDelay> orderDelayRepository,
            IMapper mapper,
            IRepository<OrderPublish> orderPublishRepository,
            ISessionContext sessionContext,
            IRepository<WorkflowTrace> workflowTraceRepository,
            IRepository<OrderScreen> orderScreenRepository,
            IOrderSecondaryHandlingApplication orderSecondaryHandlingApplication,
            ITimeLimitDomainService timeLimitDomainService,
            IRepository<SystemDicData> systemDicDataRepository,
            ISystemDicDataCacheManager sysDicDataCacheManager,
            IRepository<TrCallRecord> trCallRecordRepository,
            ISystemSettingCacheManager systemSettingCacheManager,
            IRepository<User> userRepository,
            IRepository<Account> accountRepository
            )
        {
            _orderRepository = orderRepository;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _mapper = mapper;
            _orderPublishRepository = orderPublishRepository;
            _sessionContext = sessionContext;
            _workflowTraceRepository = workflowTraceRepository;
            _orderScreenRepository = orderScreenRepository;
            _orderSecondaryHandlingApplication = orderSecondaryHandlingApplication;
            _timeLimitDomainService = timeLimitDomainService;
            _systemDicDataRepository = systemDicDataRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _trCallRecordRepository = trCallRecordRepository;
            _systemSettingCacheManager = systemSettingCacheManager;
            _userRepository = userRepository;
            _accountRepository = accountRepository;
        }
        /// <summary>
        /// 部门办件统计表---新
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<List<DepartmentalProcessingStatisticsDataDto>> DepartmentalProcessingStatisticsNew(DepartmentalProcessingStatisticsRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var IsCenter = _sessionContext.OrgIsCenter;

            //信件总量：信件总量=已办件数+在办件数+会签已办+会签待办
            //已办件数：该部门已办理完成的工单总数（含参与会签的工单），若工单被部门多次办理，只统计一次
            //在办件数：该部门还未办理的工单数（若是一级部门，需包含中心向该部门发起回签的工单）。（特殊说明：部门或者中心发起了会签，会签中还未汇总，应算发起会签部门的在办里面）

            string strSql = "select \"OrgCode\",\"Name\" \"OrgName\",\"OrgType\", \"YbOrderCountNum\", \"ZbOrderCountNum\",\"Archived\", \"ToBeArchived\", \"WaitPublished\",\"PublishedOpen\"\r\n, \"PublishedNoOpen\",\"YbOverdue\", \"ZbOverdue\", \"HqzbOverdue\", \"HqybOverdue\", \"DelayEnd\", \"DelayWait\", \"OrderDelayCount\", \"ScreenCount\", \"ScreenApproval\"\r\n, \"ScreenPass\",\"ScreenNotPass\",\"SatisfactionCount\",\"NotSatisfactionCount\", (\"YbOrderCountNum\"-\"YbOverdue\"  -\"HqybOverdue\" ) \"CompleteOnTime\"\r\nfrom (\r\nSELECT \"HandlerOrgId\" \"OrgCode\",SUM(\"YbOrderCountNum\") \"YbOrderCountNum\",SUM(\"ZbOrderCountNum\") \"ZbOrderCountNum\",SUM(\"Archived\") \"Archived\"\r\n,SUM(\"ToBeArchived\") \"ToBeArchived\",SUM(\"WaitPublished\") \"WaitPublished\",SUM(\"PublishedOpen\") \"PublishedOpen\",SUM(\"PublishedNoOpen\") \"PublishedNoOpen\"\r\n,SUM(\"YbOverdue\") \"YbOverdue\",SUM(\"ZbOverdue\") \"ZbOverdue\",SUM(\"HqzbOverdue\") \"HqzbOverdue\",SUM(\"HqybOverdue\") \"HqybOverdue\"\r\n,SUM(\"DelayEnd\") \"DelayEnd\",SUM(\"DelayWait\") \"DelayWait\",SUM(\"OrderDelayCount\") \"OrderDelayCount\",SUM(\"ScreenCount\") \"ScreenCount\"\r\n,SUM(\"ScreenApproval\") \"ScreenApproval\",SUM(\"ScreenPass\") \"ScreenPass\",SUM(\"ScreenNotPass\") \"ScreenNotPass\",SUM(\"SatisfactionCount\") \"SatisfactionCount\"\r\n,SUM(\"NotSatisfactionCount\") \"NotSatisfactionCount\"\r\nFROM (\r\n\r\n/*查询已办  已办=工单已办+会签已办*/\r\n\tselect  \"HandlerOrgId\",SUM(( CASE  WHEN ( \"HandlerOrgId\" is not NULL ) THEN 1  ELSE 0 END )) \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0 \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0 \"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0\"PublishedOpen\", 0\"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT \"Id\",\"HandlerOrgId\" FROM(\r\nSELECT  \"Id\" , SUBSTRING(\"ActualHandleOrgCode\",1,{3}) \"HandlerOrgId\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" >= 300 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\nunion ALL\r\nSELECT \"o\".\"Id\", SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND \"t\".\"Status\">=2 AND  \"t\".\"CreationTime\" <= '{1}' and \"t\".\"TraceState\" <> 10  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=FALSE AND \"t\".\"HandlerOrgId\" IS not NULL {2}\r\n\t)\"te\" group by \"Id\",\"HandlerOrgId\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}   GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询待办  待办=工单待办+会签待办*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\" ,SUM(( CASE  WHEN ( \"HandlerOrgId\" is not NULL ) THEN 1  ELSE 0 END )) \"ZbOrderCountNum\", 0 \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0 \"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0\"PublishedOpen\", 0\"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT \"Id\",\"HandlerOrgId\" FROM(\r\nSELECT  \"Id\" , SUBSTRING(\"CurrentHandleOrgId\",1,{3}) \"HandlerOrgId\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" < 300 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\nunion ALL\r\nSELECT \"o\".\"Id\", SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND \"t\".\"Status\"<2 AND  \"t\".\"CreationTime\" <= '{1}' and \"t\".\"TraceState\" <> 10 AND  \"t\".\"CountersignPosition\" > 0 AND \"t\".\"HandlerOrgId\" IS not NULL {2}\r\n\t)\"te\" group by \"Id\",\"HandlerOrgId\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n\r\n/*查询已办、已办超期、已归档、 待发布、已发布公开、已发布不公开*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", SUM(( CASE  WHEN (( \"Status\" >= 300 ) AND ( \"ActualHandleTime\" > \"ExpiredTime\" )) THEN 1  ELSE 0 END )) \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",SUM(( CASE  WHEN ( \"Status\" >= 300 ) THEN 1  ELSE 0 END ))\"Archived\" ,0 \"ToBeArchived\",SUM(( CASE  WHEN ( \"Status\" = 300 ) THEN 1  ELSE 0 END )) \"WaitPublished\",SUM(( CASE  WHEN (( \"Status\" >= 400 ) AND ( \"IsPublicity\" = true )) THEN 1  ELSE 0 END )) \"PublishedOpen\", SUM(( CASE  WHEN (( \"Status\" >= 400 ) AND ( \"IsPublicity\" = false )) THEN 1  ELSE 0 END )) \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT  \"Id\" , SUBSTRING(\"ActualHandleOrgCode\",1,{3}) \"HandlerOrgId\", \"Status\", \"ActualHandleTime\", \"ExpiredTime\",\"IsPublicity\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" > 0 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询待办、待办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",SUM(( CASE  WHEN (( \"Status\" < 300 ) AND ( \"ExpiredTime\" <NOW())) THEN 1  ELSE 0 END )) \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT  \"Id\", SUBSTRING(\"CurrentHandleOrgId\",1,{3}) \"HandlerOrgId\",\"Status\" ,\"ExpiredTime\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" > 0 and  \"CurrentHandleOrgId\" IS not NULL {2}\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询会签已办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",SUM(1) \"HqybOverdue\",0\"HqzbOverdue\",0\"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"HandleTime\" > \"t\".\"StepExpiredTime\" and \"t\".\"Status\" >= 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\" \r\n\r\nunion all\r\n\r\n/*查询会签待办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",SUM(1) \"HqzbOverdue\",0\"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and NOW() >= \"t\".\"StepExpiredTime\" and \"t\".\"Status\" < 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\n\r\nunion all\r\n\r\n/*会签已办*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0\"HqybOverdue\",0\"HqzbOverdue\",SUM(1) \"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"Status\" >= 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\n\r\nunion all\r\n\r\n/*查询会签待办*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0\"HqybOverdue\",0\"HqzbOverdue\",0\"DelayEnd\",SUM(1) \"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"Status\" < 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\nunion all \r\n/*查询延期通过数量*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",SUM(( CASE  WHEN ( \"HandlerOrgId\" IS NOT NULL ) THEN 1  ELSE 0 END ))  \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\" ,0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\" from(\r\n\t\t\tselect  SUBSTRING(\"ApplyOrgCode\",1,{3}) \"HandlerOrgId\" from \"order_delay\" \"d\"\r\n\t\t\tleft join \"order\" \"o\" on \"d\".\"OrderId\"=\"o\".\"Id\"\r\n\t\t\twhere  \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}' and \"d\".\"DelayState\"=1  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL   {4} GROUP BY \"HandlerOrgId\"\r\nunion all \r\n\t\t/*查询甄别*/\r\n\t\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",0 \"OrderDelayCount\" \r\n\t\t,SUM(( CASE  WHEN ( \"HandlerOrgId\" IS NOT NULL ) THEN 1  ELSE 0 END )) AS \"ScreenCount\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"<=1 ) THEN 1  ELSE 0 END )) AS \"ScreenApproval\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"=2 ) THEN 1  ELSE 0 END )) AS \"ScreenPass\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"=3 ) THEN 1  ELSE 0 END )) AS \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\n\t\tfrom(\r\n\t\t\tselect  SUBSTRING(\"d\".\"CreatorOrgId\",1,{3}) \"HandlerOrgId\",\"d\".\"Status\" from \"order_screen\" \"d\"\r\n\t\t\tleft join \"order\" \"o\" on \"d\".\"OrderId\"=\"o\".\"Id\"\r\n\t\t\twhere  \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}'  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL  {4} GROUP BY \"HandlerOrgId\"\r\n\t\tunion ALL\r\n\t\t/*查询满意度*/\r\n\t\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\" \r\n\t\t,SUM(( CASE  WHEN ( (\"OrgProcessingResults\"->>'Key')::VARCHAR != '2' ) THEN 1  ELSE 0 END )) AS \"SatisfactionCount\"\r\n\t\t,SUM(( CASE  WHEN ( (\"OrgProcessingResults\"->>'Key')::VARCHAR = '2'  ) THEN 1  ELSE 0 END )) AS \"NotSatisfactionCount\"\r\n\t\tFROM(\r\n\t\t\tSELECT SUBSTRING(\"VisitOrgCode\",1,{3}) \"HandlerOrgId\" ,\"OrgProcessingResults\" from \"order_visit_detail\" \"d\"\r\n\t\t\tleft join \"order_visit\" \"v\" on \"v\".\"Id\"=\"d\".\"VisitId\"\r\n\t\t\tleft join \"order\" \"o\" on \"o\".\"Id\"=\"v\".\"OrderId\"\r\n\t\t\twhere \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}' and \"d\".\"VisitTarget\"=20 and \"v\".\"VisitState\"=30 and \"d\".\"VisitOrgCode\" is not NULL  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL {4} GROUP BY \"HandlerOrgId\"\r\n)\"te\" GROUP BY \"HandlerOrgId\"\r\n) \"su\"\r\nleft join \"system_organize\" \"so\" on \"so\".\"Id\"=\"su\".\"OrgCode\"\r\nwhere 1=1 {5}\r\norder by \"su\".\"OrgCode\"";
            var orgLength = IsCenter == true ? 6 : _sessionContext.RequiredOrgId.Length + 3;
            var isOrg = IsCenter == false ? " and \"HandlerOrgId\" like concat('" + _sessionContext.RequiredOrgId + "','%') " : " ";
            string strWhere = " ";
            if (dto.IsProvince.HasValue && dto.IsProvince == true)
                strWhere += "  and \"o\".\"Source\"=100 ";

            if (dto.TypeId.HasValue && dto.TypeId == 1)
                strWhere += " and \"o\".\"IdentityType\"=1 ";
            else if (dto.TypeId.HasValue && dto.TypeId == 2)
                strWhere += " and \"o\".\"IdentityType\"=2 ";

            var orgName = string.IsNullOrEmpty(dto.OrgName) == false ? "   and   \"Name\" like concat('%" + dto.OrgName + "%')" : "";

            strSql = string.Format(strSql, dto.StartTime, dto.EndTime, strWhere, orgLength, isOrg, orgName);

            return _orderRepository.DepartmentalProcessingStatisticsNew(strSql);

        }

        /// <summary>
        /// 部门办件统计表--子级---新
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<List<DepartmentalProcessingStatisticsDataDto>> DepartmentalProcessingChildStatisticsNew(DepartmentalProcessingStatisticsRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            //信件总量：信件总量=已办件数+在办件数，工单需去重
            //已办件数：该部门已办理完成的工单总数（含参与会签的工单），若工单被部门多次办理，只统计一次
            //在办件数：该部门还未办理的工单数（若是一级部门，需包含中心向该部门发起回签的工单）。（特殊说明：部门或者中心发起了会签，会签中还未汇总，应算发起会签部门的在办里面）

            string strSql = "select \"OrgCode\",\"Name\" \"OrgName\",\"OrgType\", \"YbOrderCountNum\", \"ZbOrderCountNum\",\"Archived\", \"ToBeArchived\", \"WaitPublished\",\"PublishedOpen\"\r\n, \"PublishedNoOpen\",\"YbOverdue\", \"ZbOverdue\", \"HqzbOverdue\", \"HqybOverdue\", \"DelayEnd\", \"DelayWait\", \"OrderDelayCount\", \"ScreenCount\", \"ScreenApproval\"\r\n, \"ScreenPass\",\"ScreenNotPass\",\"SatisfactionCount\",\"NotSatisfactionCount\", (\"YbOrderCountNum\"-\"YbOverdue\"  -\"HqybOverdue\" ) \"CompleteOnTime\"\r\nfrom (\r\nSELECT \"HandlerOrgId\" \"OrgCode\",SUM(\"YbOrderCountNum\") \"YbOrderCountNum\",SUM(\"ZbOrderCountNum\") \"ZbOrderCountNum\",SUM(\"Archived\") \"Archived\"\r\n,SUM(\"ToBeArchived\") \"ToBeArchived\",SUM(\"WaitPublished\") \"WaitPublished\",SUM(\"PublishedOpen\") \"PublishedOpen\",SUM(\"PublishedNoOpen\") \"PublishedNoOpen\"\r\n,SUM(\"YbOverdue\") \"YbOverdue\",SUM(\"ZbOverdue\") \"ZbOverdue\",SUM(\"HqzbOverdue\") \"HqzbOverdue\",SUM(\"HqybOverdue\") \"HqybOverdue\"\r\n,SUM(\"DelayEnd\") \"DelayEnd\",SUM(\"DelayWait\") \"DelayWait\",SUM(\"OrderDelayCount\") \"OrderDelayCount\",SUM(\"ScreenCount\") \"ScreenCount\"\r\n,SUM(\"ScreenApproval\") \"ScreenApproval\",SUM(\"ScreenPass\") \"ScreenPass\",SUM(\"ScreenNotPass\") \"ScreenNotPass\",SUM(\"SatisfactionCount\") \"SatisfactionCount\"\r\n,SUM(\"NotSatisfactionCount\") \"NotSatisfactionCount\"\r\nFROM (\r\n\r\n/*查询已办  已办=工单已办+会签已办*/\r\n\tselect  \"HandlerOrgId\",SUM(( CASE  WHEN ( \"HandlerOrgId\" is not NULL ) THEN 1  ELSE 0 END )) \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0 \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0 \"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0\"PublishedOpen\", 0\"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT \"Id\",\"HandlerOrgId\" FROM(\r\nSELECT  \"Id\" , SUBSTRING(\"ActualHandleOrgCode\",1,{3}) \"HandlerOrgId\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" >= 300 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\nunion ALL\r\nSELECT \"o\".\"Id\", SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND \"t\".\"Status\">=2 AND  \"t\".\"CreationTime\" <= '{1}' and \"t\".\"TraceState\" <> 10  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=FALSE AND \"t\".\"HandlerOrgId\" IS not NULL {2}\r\n\t)\"te\" group by \"Id\",\"HandlerOrgId\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}   GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询待办  待办=工单待办+会签待办*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\" ,SUM(( CASE  WHEN ( \"HandlerOrgId\" is not NULL ) THEN 1  ELSE 0 END )) \"ZbOrderCountNum\", 0 \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0 \"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0\"PublishedOpen\", 0\"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT \"Id\",\"HandlerOrgId\" FROM(\r\nSELECT  \"Id\" , SUBSTRING(\"CurrentHandleOrgId\",1,{3}) \"HandlerOrgId\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" < 300 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\nunion ALL\r\nSELECT \"o\".\"Id\", SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND \"t\".\"Status\"<2 AND  \"t\".\"CreationTime\" <= '{1}' and \"t\".\"TraceState\" <> 10 AND  \"t\".\"CountersignPosition\" > 0 AND \"t\".\"HandlerOrgId\" IS not NULL {2}\r\n\t)\"te\" group by \"Id\",\"HandlerOrgId\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n\r\n/*查询已办、已办超期、已归档、 待发布、已发布公开、已发布不公开*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", SUM(( CASE  WHEN (( \"Status\" >= 300 ) AND ( \"ActualHandleTime\" > \"ExpiredTime\" )) THEN 1  ELSE 0 END )) \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",SUM(( CASE  WHEN ( \"Status\" >= 300 ) THEN 1  ELSE 0 END ))\"Archived\" ,0 \"ToBeArchived\",SUM(( CASE  WHEN ( \"Status\" = 300 ) THEN 1  ELSE 0 END )) \"WaitPublished\",SUM(( CASE  WHEN (( \"Status\" >= 400 ) AND ( \"IsPublicity\" = true )) THEN 1  ELSE 0 END )) \"PublishedOpen\", SUM(( CASE  WHEN (( \"Status\" >= 400 ) AND ( \"IsPublicity\" = false )) THEN 1  ELSE 0 END )) \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT  \"Id\" , SUBSTRING(\"ActualHandleOrgCode\",1,{3}) \"HandlerOrgId\", \"Status\", \"ActualHandleTime\", \"ExpiredTime\",\"IsPublicity\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" > 0 and  \"ActualHandleOrgCode\" IS not NULL {2}\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询待办、待办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",SUM(( CASE  WHEN (( \"Status\" < 300 ) AND ( \"ExpiredTime\" <NOW())) THEN 1  ELSE 0 END )) \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\tSELECT  \"Id\", SUBSTRING(\"CurrentHandleOrgId\",1,{3}) \"HandlerOrgId\",\"Status\" ,\"ExpiredTime\" FROM \"order\" \"o\"\r\n  WHERE  \"CreationTime\" >= '{0}'  AND  \"CreationTime\" <= '{1}'  AND  \"Status\" > 0 and  \"CurrentHandleOrgId\" IS not NULL {2}\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\"  \r\nunion ALL\r\n/*查询会签已办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",SUM(1) \"HqybOverdue\",0\"HqzbOverdue\",0\"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"HandleTime\" > \"t\".\"StepExpiredTime\" and \"t\".\"Status\" >= 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL   {4}  GROUP BY \"HandlerOrgId\" \r\n\r\nunion all\r\n\r\n/*查询会签待办超期*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0 \"HqybOverdue\",SUM(1) \"HqzbOverdue\",0\"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and NOW() >= \"t\".\"StepExpiredTime\" and \"t\".\"Status\" < 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\n\r\nunion all\r\n\r\n/*会签已办*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0\"HqybOverdue\",0\"HqzbOverdue\",SUM(1) \"DelayEnd\",0\"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"Status\" >= 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\n\r\nunion all\r\n\r\n/*查询会签待办*/\r\n\tselect  \"HandlerOrgId\",0  \"YbOrderCountNum\" ,0 \"ZbOrderCountNum\", 0  \"YbOverdue\",0 \"ZbOverdue\",0\"HqybOverdue\",0\"HqzbOverdue\",0\"DelayEnd\",SUM(1) \"DelayWait\",0\"Archived\" ,0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\" ,0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\nFROM  (\r\n\t\tselect \"HandlerOrgId\",\"Id\" from (\r\n\t\t\tSELECT SUBSTRING(\"t\".\"HandlerOrgId\",1,{3}) \"HandlerOrgId\", \"t\".\"HandleTime\" , \"t\".\"StepExpiredTime\", \"t\".\"Status\", \"t\".\"CountersignPosition\", \"o\".\"Id\"  FROM \"workflow_trace\" \"t\" \r\n\tLeft JOIN \"order\" \"o\" ON  \"t\".\"ExternalId\" = \"o\".\"Id\"  \r\n\tWHERE  \"t\".\"ModuleCode\" = 'OrderHandle' AND  \"t\".\"CreationTime\" >= '{0}' AND  \"t\".\"CreationTime\" <= '{1}'  AND  \"t\".\"CountersignPosition\" > 0 and \"t\".\"IsStartCountersign\"=false AND \"t\".\"HandlerOrgId\" IS not NULL and \"t\".\"Status\" < 2  {2}\r\n\t\t)\"s\"\r\n\tgroup by \"HandlerOrgId\" ,\"Id\"\r\n) \"s\" where \"HandlerOrgId\" IS not NULL  {4}    GROUP BY \"HandlerOrgId\"  \r\nunion all \r\n/*查询延期通过数量*/\r\n\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",SUM(( CASE  WHEN ( \"HandlerOrgId\" IS NOT NULL ) THEN 1  ELSE 0 END ))  \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\" ,0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\" from(\r\n\t\t\tselect  SUBSTRING(\"ApplyOrgCode\",1,{3}) \"HandlerOrgId\" from \"order_delay\" \"d\"\r\n\t\t\tleft join \"order\" \"o\" on \"d\".\"OrderId\"=\"o\".\"Id\"\r\n\t\t\twhere  \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}' and \"d\".\"DelayState\"=1  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL   {4} GROUP BY \"HandlerOrgId\"\r\nunion all \r\n\t\t/*查询甄别*/\r\n\t\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",0 \"OrderDelayCount\" \r\n\t\t,SUM(( CASE  WHEN ( \"HandlerOrgId\" IS NOT NULL ) THEN 1  ELSE 0 END )) AS \"ScreenCount\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"<=1 ) THEN 1  ELSE 0 END )) AS \"ScreenApproval\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"=2 ) THEN 1  ELSE 0 END )) AS \"ScreenPass\"\r\n\t\t,SUM(( CASE  WHEN ( \"Status\"=3 ) THEN 1  ELSE 0 END )) AS \"ScreenNotPass\",0 \"SatisfactionCount\", 0 \"NotSatisfactionCount\"\r\n\t\tfrom(\r\n\t\t\tselect  SUBSTRING(\"d\".\"CreatorOrgId\",1,{3}) \"HandlerOrgId\",\"d\".\"Status\" from \"order_screen\" \"d\"\r\n\t\t\tleft join \"order\" \"o\" on \"d\".\"OrderId\"=\"o\".\"Id\"\r\n\t\t\twhere  \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}'  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL  {4} GROUP BY \"HandlerOrgId\"\r\n\t\tunion ALL\r\n\t\t/*查询满意度*/\r\n\t\tselect  \"HandlerOrgId\",0 \"YbOrderCountNum\",0 \"ZbOrderCountNum\",0 \"YbOverdue\" ,0 \"ZbOverdue\",0 \"HqybOverdue\",0 \"HqzbOverdue\",0 \"DelayEnd\",0 \"DelayWait\" ,0\"Archived\",0 \"ToBeArchived\",0 \"WaitPublished\",0 \"PublishedOpen\",0 \"PublishedNoOpen\",0 \"OrderDelayCount\",0 \"ScreenCount\",0 \"ScreenApproval\", 0 \"ScreenPass\" , 0 \"ScreenNotPass\" \r\n\t\t,SUM(( CASE  WHEN ( (\"OrgProcessingResults\"->>'Key')::VARCHAR != '2' ) THEN 1  ELSE 0 END )) AS \"SatisfactionCount\"\r\n\t\t,SUM(( CASE  WHEN ( (\"OrgProcessingResults\"->>'Key')::VARCHAR = '2'  ) THEN 1  ELSE 0 END )) AS \"NotSatisfactionCount\"\r\n\t\tFROM(\r\n\t\t\tSELECT SUBSTRING(\"VisitOrgCode\",1,{3}) \"HandlerOrgId\" ,\"OrgProcessingResults\" from \"order_visit_detail\" \"d\"\r\n\t\t\tleft join \"order_visit\" \"v\" on \"v\".\"Id\"=\"d\".\"VisitId\"\r\n\t\t\tleft join \"order\" \"o\" on \"o\".\"Id\"=\"v\".\"OrderId\"\r\n\t\t\twhere \"d\".\"CreationTime\">='{0}' and \"d\".\"CreationTime\"<='{1}' and \"d\".\"VisitTarget\"=20 and \"v\".\"VisitState\"=30 and \"d\".\"VisitOrgCode\" is not NULL  {2}\r\n\t\t)\"s\" where \"HandlerOrgId\" IS not NULL {4} GROUP BY \"HandlerOrgId\"\r\n)\"te\" GROUP BY \"HandlerOrgId\"\r\n) \"su\"\r\nleft join \"system_organize\" \"so\" on \"so\".\"Id\"=\"su\".\"OrgCode\"\r\nwhere 1=1 {5}\r\norder by \"su\".\"OrgCode\"";

            string strWhere = " ";
            if (dto.IsProvince.HasValue && dto.IsProvince == true)
                strWhere += "  and \"o\".\"Source\"=100 ";

            if (dto.TypeId.HasValue && dto.TypeId == 1)
                strWhere += " and \"o\".\"IdentityType\"=1 ";
            else if (dto.TypeId.HasValue && dto.TypeId == 2)
                strWhere += " and \"o\".\"IdentityType\"=2 ";

            // var IsProvince = dto.IsProvince.HasValue && dto.IsProvince == true ? "  and \"o\".\"Source\"=100 " : " ";
            var orgLength = dto.OrgCode.Length + 3;

            var isOrg = dto.OrgCode == "001" ? "  and \"HandlerOrgId\" ='001'" : " and \"HandlerOrgId\" like concat('" + dto.OrgCode + "','%') ";

            var orgName = string.IsNullOrEmpty(dto.OrgName) == false ? "   and   \"Name\" like concat('%" + dto.OrgName + "%')" : "";

            strSql = string.Format(strSql, dto.StartTime, dto.EndTime, strWhere, orgLength, isOrg, orgName);

            return _orderRepository.DepartmentalProcessingStatisticsNew(strSql);

        }

        /// <summary>
        /// 部门办件统计表--明细---新
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<SelectOrderId> GetDepartmentalProcessingStatisticsListNew(DepartmentalProcessingStatisticsRequest dto, CancellationToken cancellationToken)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            RefAsync<int> total = 0;
            ISugarQueryable<SelectOrderId> query = null;
            switch (dto.StatisticsType)
            {
                case EStatisticsType.YBOrderCountNum:
                    //工单已办
                    var orderybquery = _orderRepository.Queryable()
                              .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Filed)
                              .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                              .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                               .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                               .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                               .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                               .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                                .Select(o => new SelectOrderId { Id = o.Id })
                             .MergeTable();
                    //会签已办
                    var hqybquery = _workflowTraceRepository.Queryable()
                               .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                                .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                                && t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None
                                && t.TraceState != EWorkflowTraceState.StepRemoveByPrevious && t.IsStartCountersign == false)
                                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                                .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                             .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                             .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                                .GroupBy((t, o) => o.Id)
                                .Select((t, o) => new SelectOrderId { Id = o.Id })
                                .MergeTable();
                    query = _orderRepository.UnionAll(orderybquery, hqybquery).GroupBy(p => p.Id).MergeTable();
                    break;
                case EStatisticsType.ZBOrderCountNum:
                    //工单在办
                    var orderzbquery = _orderRepository.Queryable()
                             .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status < EOrderStatus.Filed)
                             .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                              .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                              .WhereIF(dto.OrgCode == "001", o => o.CurrentHandleOrgId == dto.OrgCode)
                              .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.CurrentHandleOrgId.StartsWith(dto.OrgCode))
                              .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.CurrentHandleOrgId == dto.OrgCode)
                               .Select(o => new SelectOrderId { Id = o.Id })
                            .MergeTable();
                    //会签在办
                    var hqzbquery = _workflowTraceRepository.Queryable()
                              .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                               .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                               && t.Status < EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None && t.IsStartCountersign == false)
                               .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                               .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                               .GroupBy((t, o) => o.Id)
                               .Select((t, o) => new SelectOrderId { Id = o.Id })
                               .MergeTable();
                    query = _orderRepository.UnionAll(orderzbquery, hqzbquery).GroupBy(p => p.Id).MergeTable();
                    break;
                case EStatisticsType.OrderDelayCount://延期次数
                    query = _orderDelayRepository.Queryable()
                          .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
                        .Where((d, o) => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && d.DelayState == EDelayState.Pass)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
                          .WhereIF(dto.TypeId != null && dto.TypeId == 1, (d, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (d, o) => o.IdentityType == EIdentityType.Enterprise)
                        .WhereIF(dto.OrgCode == "001", (d, o) => d.ApplyOrgCode == dto.OrgCode)
                        .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.ApplyOrgCode.StartsWith(dto.OrgCode))
                        .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.ApplyOrgCode == dto.OrgCode)
                        .OrderByDescending((d, o) => o.CreationTime)
                        .Select((d, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.YBOverdue://已办超期
                    query = _orderRepository.Queryable()
                         .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Filed && o.ActualHandleTime > o.ExpiredTime)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                          .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                          .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                          .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                          .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                           .Select(o => new SelectOrderId { Id = o.Id })
                        .MergeTable();
                    break;
                case EStatisticsType.ZBOverdue://待办超期
                    query = _orderRepository.Queryable()
                          .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status < EOrderStatus.Filed && o.ExpiredTime < DateTime.Now)
                          .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                          .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                           .WhereIF(dto.OrgCode == "001", o => o.CurrentHandleOrgId == dto.OrgCode)
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.CurrentHandleOrgId.StartsWith(dto.OrgCode))
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.CurrentHandleOrgId == dto.OrgCode)
                            .Select(o => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.HQYBOverdue://会签已办超期
                    query = _workflowTraceRepository.Queryable()
                        .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                         .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                         && t.Status >= EWorkflowStepStatus.Handled && t.HandleTime > t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None
                         && t.TraceState != EWorkflowTraceState.StepRemoveByPrevious)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.HQZBOverdue://会签待办超期
                    query = _workflowTraceRepository.Queryable()
                        .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                         .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                         && t.Status < EWorkflowStepStatus.Handled && DateTime.Now > t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.SubtotalOverdue://超期件数
                    //已办超期
                    var ybcqquery = _orderRepository.Queryable()
                        .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Filed && o.ActualHandleTime > o.ExpiredTime)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                        .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                          .Select(o => new SelectOrderId { Id = o.Id })
                          .MergeTable();
                    //待办超期
                    var dbcqquery = _orderRepository.Queryable()
                          .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status < EOrderStatus.Filed && o.ExpiredTime < DateTime.Now)
                          .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                          .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                           .WhereIF(dto.OrgCode == "001", o => o.CurrentHandleOrgId == dto.OrgCode)
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.CurrentHandleOrgId.StartsWith(dto.OrgCode))
                           .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.CurrentHandleOrgId == dto.OrgCode)
                            .Select(o => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    //会签已办超期
                    var hqybcqquery = _workflowTraceRepository.Queryable()
                        .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                         .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                         && t.Status >= EWorkflowStepStatus.Handled && t.HandleTime > t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None
                         && t.TraceState != EWorkflowTraceState.StepRemoveByPrevious)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                           .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    var hqdbcqquery = _workflowTraceRepository.Queryable()
                         .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                          .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                          && t.Status < EWorkflowStepStatus.Handled && DateTime.Now > t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None)
                          .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                            .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                          .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                          .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                          .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                          .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                          .GroupBy((t, o) => o.Id)
                          .Select((t, o) => new SelectOrderId { Id = o.Id })
                          .MergeTable();
                    query = _orderRepository.UnionAll(ybcqquery, dbcqquery, hqybcqquery, hqdbcqquery).GroupBy(p => p.Id).MergeTable();
                    break;
                case EStatisticsType.DelayEnd://会签已办
                    query = _workflowTraceRepository.Queryable()
                           .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                            .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                            && t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None
                            && t.TraceState != EWorkflowTraceState.StepRemoveByPrevious && t.IsStartCountersign == false)
                            .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                              .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                            .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                            .GroupBy((t, o) => o.Id)
                            .Select((t, o) => new SelectOrderId { Id = o.Id })
                            .MergeTable();
                    break;
                case EStatisticsType.DelayWait://会签待办
                    query = _workflowTraceRepository.Queryable()
                            .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                             .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime
                             && t.Status < EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None && t.IsStartCountersign == false)
                             .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                               .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
                             .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
                             .GroupBy((t, o) => o.Id)
                             .Select((t, o) => new SelectOrderId { Id = o.Id })
                             .MergeTable();
                    break;
                case EStatisticsType.ToBeArchived://待归档没得数据
                    query = _orderRepository.Queryable()
                            .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status < EOrderStatus.WaitForAccept)
                           .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                            .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                             .Select(o => new SelectOrderId { Id = o.Id })
                          .MergeTable();
                    break;
                case EStatisticsType.Archived://已归档
                    query = _orderRepository.Queryable()
                          .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Filed)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                          .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                           .Select(o => new SelectOrderId { Id = o.Id })
                        .MergeTable();
                    break;
                case EStatisticsType.WaitPublished://待发布
                    query = _orderRepository.Queryable()
                         .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status == EOrderStatus.Filed)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                          .Select(o => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.PublishedOpen://发布公开
                    query = _orderRepository.Queryable()
                         .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Published && o.IsPublicity == true)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                         .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                          .Select(o => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.PublishedNoOpen://发布不公开
                    query = _orderRepository.Queryable()
                        .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Published && o.IsPublicity == false)
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                        .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                            .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
                         .Select(o => new SelectOrderId { Id = o.Id })
                      .MergeTable();
                    break;
                case EStatisticsType.ScreenCount://甄别总量
                    query = _orderScreenRepository.Queryable()
                        .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
                       .Where((d, o) => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && o.Id != null)
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 1, (d, o) => o.IdentityType == EIdentityType.Citizen)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 2, (d, o) => o.IdentityType == EIdentityType.Enterprise)
                       .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
                        .Select((d, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenApproval://带甄别
                    query = _orderScreenRepository.Queryable()
                       .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
                       .Where((d, o) => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && o.Id != null && (d.Status == EScreenStatus.Approval || d.Status == EScreenStatus.Apply))
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 1, (d, o) => o.IdentityType == EIdentityType.Citizen)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 2, (d, o) => o.IdentityType == EIdentityType.Enterprise)
                       .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
                        .Select((d, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenPass://甄别通过
                    query = _orderScreenRepository.Queryable()
                       .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
                       .Where((d, o) => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && o.Id != null && d.Status == EScreenStatus.End)
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 1, (d, o) => o.IdentityType == EIdentityType.Citizen)
                       .WhereIF(dto.TypeId != null && dto.TypeId == 2, (d, o) => o.IdentityType == EIdentityType.Enterprise)
                       .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
                        .Select((d, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenNotPass://甄别不通过
                    query = _orderScreenRepository.Queryable()
                      .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
                      .Where((d, o) => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && o.Id != null && d.Status == EScreenStatus.Refuse)
                      .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
                      .WhereIF(dto.TypeId != null && dto.TypeId == 1, (d, o) => o.IdentityType == EIdentityType.Citizen)
                      .WhereIF(dto.TypeId != null && dto.TypeId == 2, (d, o) => o.IdentityType == EIdentityType.Enterprise)
                      .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
                       .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
                        .Select((d, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                default:
                    break;
            }
            return query;
        }

        /// <summary>
        /// 部门办件统计明细表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<SelectOrderId> DepartmentalProcessingStatisticsDetailsList(DepartmentalProcessingStatisticsRequest dto)
        {
            //var query = _workflowTraceRepository.Queryable()
            //            .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
            //             .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime)
            //             .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
            //                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, (t, o) => o.IdentityType == EIdentityType.Citizen)
            //             .WhereIF(dto.TypeId != null && dto.TypeId == 2, (t, o) => o.IdentityType == EIdentityType.Enterprise)
            //             // .WhereIF(IsCenter, (t, o) => t.HandlerOrgId == dto.OrgCode)
            //             .WhereIF(!_sessionContext.OrgIsCenter, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
            //             .GroupBy((t, o) => o.Id)
            //             .Select((t, o) => new SelectOrderId { Id = o.Id })
            //             .MergeTable();
            var query = _orderRepository.Queryable()
                         .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status > EOrderStatus.WaitForAccept)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                             .WhereIF(dto.TypeId != null && dto.TypeId == 1, o => o.IdentityType == EIdentityType.Citizen)
                         .WhereIF(dto.TypeId != null && dto.TypeId == 2, o => o.IdentityType == EIdentityType.Enterprise)
                         // .WhereIF(IsCenter, (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(!_sessionContext.OrgIsCenter, o => o.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                         .Select(o => new SelectOrderId { Id = o.Id })
                         .MergeTable();

            return query;
        }

        /// <summary>
        /// 部门办件统计表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<DepartmentalProcessingStatisticsDataDto> DepartmentalProcessingStatistics(DepartmentalProcessingStatisticsRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var IsCenter = _sessionContext.OrgIsCenter;

            //工单   已办=归完档的工单数+会签已办数量
            #region 工单
            var queryOrder = _orderRepository.Queryable()
                         .Where(it => it.CreationTime >= dto.StartTime && it.CreationTime <= dto.EndTime && it.Status > EOrderStatus.WaitForAccept)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, it => it.Source == ESource.ProvinceStraight)
                         .Select(it => new
                         {
                             it.Id,
                             OrgCode = IsCenter == true ? it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : it.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                             it.ActualHandleOrgCode,
                             it.Status,//工单状态
                             it.ExpiredTime,//期满时间
                             it.ActualHandleTime,//办理时间
                             it.CounterSignType,//会签
                         })
                        .MergeTable()
                         .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                         .GroupBy(it => new
                         {
                             it.OrgCode
                         })
                         .Select(it => new DepartmentalProcessingStatisticsDataDto
                         {
                             OrgCode = it.OrgCode,
                             // OrderCountNum = SqlFunc.AggregateCount(it.OrgCode),//总量
                             YbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已办// SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已办
                             ZbOrderCountNum = 0, //ZbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed, 1, 0)),//在办
                             Archived = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已归档
                             ToBeArchived = 0,
                             WaitPublished = 0,
                             PublishedOpen = 0,
                             PublishedNoOpen = 0,
                             YbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//已办超期
                             ZbOverdue = 0,// SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//待办超期
                             CompleteOnTime = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime <= it.ExpiredTime, 1, 0)),//按时办结
                             HqybOverdue = 0,
                             HqzbOverdue = 0,
                             DelayEnd = 0,
                             DelayWait = 0,
                             OrderDelayCount = 0,
                             ScreenCount = 0,
                             ScreenApproval = 0,
                             ScreenPass = 0,
                             ScreenNotPass = 0,
                             SatisfactionCount = 0,
                             NotSatisfactionCount = 0
                         })
                         .MergeTable();
            #endregion

            #region 发布
            //发布
            var queryPublish = _orderPublishRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .Select((x, o) => new
                {
                    o.Id,
                    OrgCode = IsCenter == true ? o.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : o.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    x.PublishState,
                    o.Status
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(it => new { it.OrgCode })
                .Select(it => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = it.OrgCode,
                    //  OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status == EOrderStatus.Filed, 1, 0)),//待发布  --已归档的就是待发布
                    PublishedOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && it.PublishState, 1, 0)),//已发布公开
                    PublishedNoOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && !it.PublishState, 1, 0)),//已发布不公开
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 会签(已办超期、待办超期)
            //会签(已办超期、待办超期)
            //var queryCountersign = _workflowStepHandleRepository.Queryable()
            // .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
            var queryCountersign = _workflowTraceRepository.Queryable()
             .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
             .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime)
             .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
             .Select((t, o) => new
             {
                 OrgCode = IsCenter == true ? t.HandlerOrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : t.HandlerOrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),// d.OrgId,
                 t.HandleTime,
                 t.StepExpiredTime,
                 t.Status,
                 t.CountersignPosition,
                 o.Id
             })
              .MergeTable()
              .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
             .GroupBy(d => new { d.OrgCode })
             .Select(d => new DepartmentalProcessingStatisticsDataDto
             {
                 OrgCode = d.OrgCode,
                 //OrderCountNum = 0,//总量
                 YbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled, 1, 0)),// 0,//已办
                 ZbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EWorkflowStepStatus.Handled, 1, 0)),// 0,//在办
                 Archived = 0,
                 ToBeArchived = 0,
                 WaitPublished = 0,
                 PublishedOpen = 0,
                 PublishedNoOpen = 0,
                 YbOverdue = 0,
                 ZbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition == ECountersignPosition.None && d.Status < EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),// 0,
                 CompleteOnTime = 0,
                 HqybOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled && d.HandleTime > d.StepExpiredTime, 1, 0)),
                 HqzbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status < EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),
                 DelayEnd = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled, 1, 0)),
                 DelayWait = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status < EWorkflowStepStatus.Handled, 1, 0)),
                 OrderDelayCount = 0,
                 ScreenCount = 0,
                 ScreenApproval = 0,
                 ScreenPass = 0,
                 ScreenNotPass = 0,
                 SatisfactionCount = 0,
                 NotSatisfactionCount = 0,
             })
            .MergeTable();
            #endregion

            #region 延期
            //延期
            var orderDelay = _orderDelayRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .Select((x, o) => new
                {
                    OrgCode = IsCenter == true ? x.ApplyOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.ApplyOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    x.DelayState
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    //  OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)),
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                 .MergeTable();
            #endregion

            #region 甄别
            //甄别
            var orderScreen = _orderScreenRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), (x, o) => x.CreatorOrgId.Contains(dto.OrgName))
                .Select((x, o) => new
                {
                    x.Id,
                    OrgCode = IsCenter == true ? x.CreatorOrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.CreatorOrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    x.Status
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    // OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Id != null, 1, 0)),
                    ScreenApproval = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply, 1, 0)),
                    ScreenPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.End, 1, 0)),
                    ScreenNotPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Refuse, 1, 0)),
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 满意度
            //满意度
            var orderVisit = _orderVisitDetailRepository.Queryable()
                .LeftJoin<OrderVisit>((x, o) => x.VisitId == o.Id)
                .LeftJoin<Order>((x, o, p) => o.OrderId == p.Id)
            .Where((x, o, p) => x.OrderVisit.VisitTime >= dto.StartTime && x.OrderVisit.VisitTime <= dto.EndTime && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
            .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o, p) => p.Source == ESource.ProvinceStraight)
            .Select((x, o, p) => new
            {
                OrgCode = IsCenter == true ? x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.VisitOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                x.OrgProcessingResults
            })
            .MergeTable()
            .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
            .GroupBy(x => new { x.OrgCode })
            .Select(x => new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = x.OrgCode,
                //  OrderCountNum = 0,//总量
                YbOrderCountNum = 0,//已办
                ZbOrderCountNum = 0,//在办
                Archived = 0,
                ToBeArchived = 0,
                WaitPublished = 0,
                PublishedOpen = 0,
                PublishedNoOpen = 0,
                YbOverdue = 0,
                ZbOverdue = 0,
                CompleteOnTime = 0,
                HqybOverdue = 0,
                HqzbOverdue = 0,
                DelayEnd = 0,
                DelayWait = 0,
                OrderDelayCount = 0,
                ScreenCount = 0,
                ScreenApproval = 0,
                ScreenPass = 0,
                ScreenNotPass = 0,
                SatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1, 0)),
                NotSatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0))
            })
          .MergeTable();
            #endregion

            //联合查询
            return _orderRepository.OrderUnionAll(queryOrder, queryPublish, queryCountersign, orderDelay, orderScreen, orderVisit)
                   .LeftJoin<SystemOrganize>((p, o) => p.OrgCode == o.Id)
                  .Select((p, o) => new DepartmentalProcessingStatisticsDataDto
                  {
                      OrgCode = p.OrgCode,
                      OrgName = o.Name,
                      OrgType = o.OrgType,
                      //OrderCountNum = p.OrderCountNum,
                      YbOrderCountNum = p.YbOrderCountNum,
                      ZbOrderCountNum = p.ZbOrderCountNum,
                      Archived = p.Archived,
                      ToBeArchived = p.ToBeArchived,
                      WaitPublished = p.WaitPublished,
                      PublishedOpen = p.PublishedOpen,
                      PublishedNoOpen = p.PublishedNoOpen,
                      YbOverdue = p.YbOverdue,
                      ZbOverdue = p.ZbOverdue,
                      CompleteOnTime = p.CompleteOnTime,
                      HqybOverdue = p.HqybOverdue,
                      HqzbOverdue = p.HqzbOverdue,
                      DelayEnd = p.DelayEnd,
                      DelayWait = p.DelayWait,
                      OrderDelayCount = p.OrderDelayCount,
                      ScreenCount = p.ScreenCount,
                      ScreenApproval = p.ScreenApproval,
                      ScreenPass = p.ScreenPass,
                      ScreenNotPass = p.ScreenNotPass,
                      SatisfactionCount = p.SatisfactionCount,
                      NotSatisfactionCount = p.NotSatisfactionCount
                  })
                  .MergeTable()
                  .WhereIF(string.IsNullOrEmpty(dto.OrgName) == false, x => x.OrgName.Contains(dto.OrgName));
        }

        /// <summary>
        /// 部门办件统计表--子级
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<DepartmentalProcessingStatisticsDataDto> DepartmentalProcessingChildStatistics(DepartmentalProcessingStatisticsRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            #region 工单
            //工单
            var queryOrder = _orderRepository.Queryable()
                      .Where(it => it.CreationTime >= dto.StartTime && it.CreationTime <= dto.EndTime && it.Status > EOrderStatus.WaitForAccept)
                      .WhereIF(dto.OrgCode == "001", it => it.ActualHandleOrgCode == dto.OrgCode)
                      .WhereIF(dto.OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                      .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, it => it.Source == ESource.ProvinceStraight)
                      .Select(it => new
                      {
                          it.Id,
                          OrgCode = it.ActualHandleOrgCode.Substring(0, dto.OrgCode.Length + 3),
                          it.ActualHandleOrgCode,
                          it.Status,//工单状态
                          it.ExpiredTime,//期满时间
                          it.ActualHandleTime,//办理时间
                          it.CounterSignType,//会签
                      })
                     .MergeTable()
                      .GroupBy(it => new
                      {
                          it.OrgCode
                      })
                      .Select(it => new DepartmentalProcessingStatisticsDataDto
                      {
                          OrgCode = it.OrgCode,
                          //  OrderCountNum = SqlFunc.AggregateCount(it.OrgCode),//总量
                          YbOrderCountNum = 0,//SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已办
                          ZbOrderCountNum = 0,//  ZbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed, 1, 0)),//在办
                          Archived = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已归档
                          ToBeArchived = 0,
                          WaitPublished = 0,
                          PublishedOpen = 0,
                          PublishedNoOpen = 0,
                          YbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//已办超期
                          ZbOverdue = 0,// SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//待办超期
                          CompleteOnTime = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime <= it.ExpiredTime, 1, 0)),//按时办结
                          HqybOverdue = 0,
                          HqzbOverdue = 0,
                          DelayEnd = 0,
                          DelayWait = 0,
                          OrderDelayCount = 0,
                          ScreenCount = 0,
                          ScreenApproval = 0,
                          ScreenPass = 0,
                          ScreenNotPass = 0,
                          SatisfactionCount = 0,
                          NotSatisfactionCount = 0
                      })
                      .MergeTable();
            #endregion

            #region 发布
            //发布
            var queryPublish = _orderPublishRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.OrgCode == "001", (x, o) => o.ActualHandleOrgCode == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                .Select((x, o) => new
                {
                    o.Id,
                    OrgCode = o.ActualHandleOrgCode.Substring(0, dto.OrgCode.Length + 3),
                    PublishState = x.PublishState,
                    Status = o.Status
                })
                .MergeTable()
                .GroupBy(it => new { it.OrgCode })
                .Select(it => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = it.OrgCode,
                    // OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status == EOrderStatus.Filed, 1, 0)),//待发布  --已归档的就是待发布
                    PublishedOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && it.PublishState, 1, 0)),//已发布公开
                    PublishedNoOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && !it.PublishState, 1, 0)),//已发布不公开
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 会签(已办超期、待办超期)
            //会签(已办超期、待办超期)
            //var queryCountersign = _workflowStepHandleRepository.Queryable()
            // .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
            var queryCountersign = _workflowTraceRepository.Queryable()
             .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
             .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime)
             .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
             .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
              .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
             .Select((t, o) => new
             {
                 OrgCode = t.HandlerOrgId.Substring(0, dto.OrgCode.Length + 3),
                 t.HandleTime,
                 t.StepExpiredTime,
                 t.Status,
                 t.CountersignPosition,
                 o.Id
             })
              .MergeTable()
             .GroupBy(d => new { d.OrgCode })
             .Select(d => new DepartmentalProcessingStatisticsDataDto
             {
                 OrgCode = d.OrgCode,
                 // OrderCountNum = 0,//总量
                 YbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled, 1, 0)),//已办
                 ZbOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EWorkflowStepStatus.Handled, 1, 0)),// 0,//在办
                 Archived = 0,
                 ToBeArchived = 0,
                 WaitPublished = 0,
                 PublishedOpen = 0,
                 PublishedNoOpen = 0,
                 YbOverdue = 0,
                 ZbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),// 0, 
                 CompleteOnTime = 0,
                 HqybOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled && d.HandleTime > d.StepExpiredTime, 1, 0)),
                 HqzbOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status < EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),
                 DelayEnd = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status >= EWorkflowStepStatus.Handled, 1, 0)),
                 DelayWait = SqlFunc.AggregateSum(SqlFunc.IIF(d.CountersignPosition > ECountersignPosition.None && d.Status < EWorkflowStepStatus.Handled, 1, 0)),
                 OrderDelayCount = 0,
                 ScreenCount = 0,
                 ScreenApproval = 0,
                 ScreenPass = 0,
                 ScreenNotPass = 0,
                 SatisfactionCount = 0,
                 NotSatisfactionCount = 0,
             })
            .MergeTable();
            #endregion

            #region 延期
            //延期
            var orderDelay = _orderDelayRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.OrgCode == "001", (x, o) => x.ApplyOrgCode == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.ApplyOrgCode.StartsWith(dto.OrgCode))
                .Select((x, o) => new
                {
                    OrgCode = x.ApplyOrgCode.Substring(0, dto.OrgCode.Length + 3),
                    x.DelayState
                })
                .MergeTable()
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    //  OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)),
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                 .MergeTable();
            #endregion

            #region 甄别
            //甄别
            var orderScreen = _orderScreenRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), (x, o) => x.CreatorOrgId.Contains(dto.OrgName))
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.OrgCode == "001", (x, o) => x.CreatorOrgId == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.CreatorOrgId.StartsWith(dto.OrgCode))
                .Select((x, o) => new
                {
                    x.Id,
                    OrgCode = x.CreatorOrgId.Substring(0, dto.OrgCode.Length + 3),
                    x.Status
                })
                .MergeTable()
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    //  OrderCountNum = 0,//总量
                    YbOrderCountNum = 0,//已办
                    ZbOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YbOverdue = 0,
                    ZbOverdue = 0,
                    CompleteOnTime = 0,
                    HqybOverdue = 0,
                    HqzbOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Id != null, 1, 0)),
                    ScreenApproval = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply, 1, 0)),
                    ScreenPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.End, 1, 0)),
                    ScreenNotPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Refuse, 1, 0)),
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 满意度
            //满意度
            var orderVisit = _orderVisitDetailRepository.Queryable()
                .LeftJoin<OrderVisit>((x, o) => x.VisitId == o.Id)
                .LeftJoin<Order>((x, o, p) => o.OrderId == p.Id)
                .Where((x, o) => x.OrderVisit.VisitTime >= dto.StartTime && x.OrderVisit.VisitTime <= dto.EndTime && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
                .WhereIF(dto.OrgCode == "001", (x, o) => x.VisitOrgCode == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.VisitOrgCode.StartsWith(dto.OrgCode))
               .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o, p) => p.Source == ESource.ProvinceStraight)
            .Select((x, o) => new
            {
                OrgCode = x.VisitOrgCode.Substring(0, dto.OrgCode.Length + 3),
                x.OrgProcessingResults
            })
            .MergeTable()
            .GroupBy(x => new { x.OrgCode })
            .Select(x => new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = x.OrgCode,
                // OrderCountNum = 0,//总量
                YbOrderCountNum = 0,//已办
                ZbOrderCountNum = 0,//在办
                Archived = 0,
                ToBeArchived = 0,
                WaitPublished = 0,
                PublishedOpen = 0,
                PublishedNoOpen = 0,
                YbOverdue = 0,
                ZbOverdue = 0,
                CompleteOnTime = 0,
                HqybOverdue = 0,
                HqzbOverdue = 0,
                DelayEnd = 0,
                DelayWait = 0,
                OrderDelayCount = 0,
                ScreenCount = 0,
                ScreenApproval = 0,
                ScreenPass = 0,
                ScreenNotPass = 0,
                SatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1, 0)),
                NotSatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0))
            })
          .MergeTable();
            #endregion

            //联合查询
            return _orderRepository.OrderUnionAll(queryOrder, queryPublish, queryCountersign, orderDelay, orderScreen, orderVisit)
                 .LeftJoin<SystemOrganize>((p, o) => p.OrgCode == o.Id)
                .Select((p, o) => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = p.OrgCode,
                    OrgName = o.Name,
                    OrgType = o.OrgType,
                    //OrderCountNum = p.OrderCountNum,
                    YbOrderCountNum = p.YbOrderCountNum,
                    ZbOrderCountNum = p.ZbOrderCountNum,
                    Archived = p.Archived,
                    ToBeArchived = p.ToBeArchived,
                    WaitPublished = p.WaitPublished,
                    PublishedOpen = p.PublishedOpen,
                    PublishedNoOpen = p.PublishedNoOpen,
                    YbOverdue = p.YbOverdue,
                    ZbOverdue = p.ZbOverdue,
                    CompleteOnTime = p.CompleteOnTime,
                    HqybOverdue = p.HqybOverdue,
                    HqzbOverdue = p.HqzbOverdue,
                    DelayEnd = p.DelayEnd,
                    DelayWait = p.DelayWait,
                    OrderDelayCount = p.OrderDelayCount,
                    ScreenCount = p.ScreenCount,
                    ScreenApproval = p.ScreenApproval,
                    ScreenPass = p.ScreenPass,
                    ScreenNotPass = p.ScreenNotPass,
                    SatisfactionCount = p.SatisfactionCount,
                    NotSatisfactionCount = p.NotSatisfactionCount
                })
                .MergeTable()
                .WhereIF(string.IsNullOrEmpty(dto.OrgName) == false, x => x.OrgName.Contains(dto.OrgName));
        }

        /// <summary>
        /// 部门办件统计表--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<SelectOrderId> GetDepartmentalProcessingStatisticsList(DepartmentalProcessingStatisticsRequest dto, CancellationToken cancellationToken)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            RefAsync<int> total = 0;
            string stye = "";
            switch (dto.StatisticsType)
            {
                case EStatisticsType.YBOrderCountNum:
                    //工单
                    stye = "0";
                    break;
                case EStatisticsType.ToBeArchived:
                case EStatisticsType.Archived:
                case EStatisticsType.YBOverdue:
                    //工单
                    stye = "1";
                    break;

                case EStatisticsType.WaitPublished:
                case EStatisticsType.PublishedOpen:
                case EStatisticsType.PublishedNoOpen:
                    //发布
                    stye = "2";
                    break;

                case EStatisticsType.OrderDelayCount:
                    //延期
                    stye = "3";
                    break;

                case EStatisticsType.ScreenCount:
                case EStatisticsType.ScreenApproval:
                case EStatisticsType.ScreenPass:
                case EStatisticsType.ScreenNotPass:
                    //甄别
                    stye = "4";
                    break;

                case EStatisticsType.HQYBOverdue:
                case EStatisticsType.HQZBOverdue:
                case EStatisticsType.DelayEnd:
                case EStatisticsType.DelayWait:
                case EStatisticsType.ZBOrderCountNum:
                case EStatisticsType.ZBOverdue:
                    //会签
                    stye = "5";
                    break;

                case EStatisticsType.SubtotalOverdue:
                    //超期总数
                    stye = "6";
                    break;

                default:
                    break;
            }
            #region 工单已办
            if (stye == "0")
            {
                var ybQuery = _orderRepository.Queryable()
                                  .Where(it => it.CreationTime >= dto.StartTime && it.CreationTime <= dto.EndTime && it.Status > EOrderStatus.WaitForAccept)
                                  .WhereIF(dto.OrgCode == "001", it => it.ActualHandleOrgCode == dto.OrgCode)
                                  .WhereIF(dto.OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                                 .WhereIF(dto.StatisticsType == EStatisticsType.YBOrderCountNum, it => it.Status >= EOrderStatus.Filed)//已办
                                 .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, it => it.Source == ESource.ProvinceStraight)
                                 .Select(it => new SelectOrderId { Id = it.Id })
                                 .MergeTable();

                //var hqybquery = _workflowStepHandleRepository.Queryable()
                //  .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
                var hqybquery = _workflowTraceRepository.Queryable()
                    .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
                  .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime)
                  .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                  .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                  .WhereIF(dto.StatisticsType == EStatisticsType.YBOrderCountNum, (t, o) => t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None)//会签已办
                  .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
                  .Select((t, o) => new SelectOrderId { Id = o.Id })
                  .MergeTable();

                var queryData = _orderRepository.OrderListUnionAll(ybQuery, hqybquery).GroupBy(p => p.Id).MergeTable();

                return queryData;
            }
            #endregion

            #region 工单
            if (stye == "1")
            {
                //工单
                var queryData = _orderRepository.Queryable()
                          .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status > EOrderStatus.WaitForAccept)
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
                          .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                          .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                           .WhereIF(dto.StatisticsType == EStatisticsType.Archived, o => o.Status >= EOrderStatus.Filed)//已归档
                            .WhereIF(dto.StatisticsType == EStatisticsType.ToBeArchived, o => o.Status < EOrderStatus.WaitForAccept)//待归档--没得待归档数据
                           .WhereIF(dto.StatisticsType == EStatisticsType.YBOverdue, o => o.Status >= EOrderStatus.Filed && o.ActualHandleTime > o.ExpiredTime)//已办超期
                           .OrderByDescending(o => o.CreationTime)
                           .Select(o => new SelectOrderId { Id = o.Id })
                        .MergeTable();

                return queryData;
            }
            #endregion

            #region 发布
            if (stye == "2")
            {
                //发布
                var queryPublish = _orderPublishRepository.Queryable()
                    .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                    .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                    .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                    .WhereIF(dto.OrgCode == "001", (x, o) => o.ActualHandleOrgCode == dto.OrgCode)
                    .WhereIF(dto.OrgCode != "001", (x, o) => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                    .WhereIF(dto.StatisticsType == EStatisticsType.WaitPublished, (x, o) => o.Status == EOrderStatus.Filed)//待发布  --已归档的就是待发布
                    .WhereIF(dto.StatisticsType == EStatisticsType.PublishedOpen, (x, o) => o.Status >= EOrderStatus.Published && x.PublishState)//已发布公开
                    .WhereIF(dto.StatisticsType == EStatisticsType.PublishedNoOpen, (x, o) => o.Status == EOrderStatus.Published && !x.PublishState)//已发布不公开
                    .OrderByDescending((x, o) => o.CreationTime)
                     .Select((x, o) => new SelectOrderId { Id = o.Id })
                     .MergeTable();

                return queryPublish;
            }
            #endregion

            #region 延期
            if (stye == "3")
            {
                //延期
                var orderDelay = _orderDelayRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.OrgCode == "001", (x, o) => x.ApplyOrgCode == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.ApplyOrgCode.StartsWith(dto.OrgCode))
                .WhereIF(dto.StatisticsType == EStatisticsType.OrderDelayCount, (x, o) => x.DelayState == EDelayState.Pass)//延期次数
                .OrderByDescending((x, o) => o.CreationTime)
                .Select((x, o) => new SelectOrderId { Id = o.Id })
               .MergeTable();

                return orderDelay;
            }
            #endregion

            #region 甄别
            if (stye == "4")
            {
                //甄别
                var orderScreen = _orderScreenRepository.Queryable()
                 .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (x, o) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.OrgCode == "001", (x, o) => x.CreatorOrgId == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.CreatorOrgId.StartsWith(dto.OrgCode))
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenCount, (x, o) => x.Id != null)//申请总量
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenApproval, (x, o) => x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply)//待甄别
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenPass, (x, o) => x.Status == EScreenStatus.End)//甄别通过
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenNotPass, (x, o) => x.Status == EScreenStatus.Refuse)//甄别不通过
                 .OrderByDescending((x, o) => o.CreationTime)
                 .Select((x, o) => new SelectOrderId { Id = o.Id })
                .MergeTable();

                return orderScreen;
            }
            #endregion

            #region 会签(已办超期、待办超期)
            if (stye == "5")
            {
                //会签(已办超期、待办超期)
                var queryCountersign = _workflowTraceRepository.Queryable()
               .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
               .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime)
               .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
               .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
               .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
               .WhereIF(dto.StatisticsType == EStatisticsType.HQYBOverdue, (t, o) => t.Status >= EWorkflowStepStatus.Handled && t.HandleTime > t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None)//会签已办超期
               .WhereIF(dto.StatisticsType == EStatisticsType.HQZBOverdue, (t, o) => t.Status < EWorkflowStepStatus.Handled && DateTime.Now >= t.StepExpiredTime && t.CountersignPosition > ECountersignPosition.None)//会签待办超期
               .WhereIF(dto.StatisticsType == EStatisticsType.DelayEnd, (t, o) => t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None)//会签已办
               .WhereIF(dto.StatisticsType == EStatisticsType.DelayWait, (t, o) => t.Status < EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None)//会签待办
               .WhereIF(dto.StatisticsType == EStatisticsType.ZBOrderCountNum, (t, o) => t.Status < EWorkflowStepStatus.Handled)//在办总量
                .WhereIF(dto.StatisticsType == EStatisticsType.ZBOverdue, (t, o) => t.Status < EWorkflowStepStatus.Handled && DateTime.Now >= t.StepExpiredTime)//在办超期
               .OrderByDescending((t, o) => o.CreationTime)
                .Select((t, o) => new SelectOrderId { Id = o.Id })
                .MergeTable()
                .GroupBy(p => p.Id)
                .MergeTable();

                return queryCountersign;
            }

            #endregion

            #region 超期件数
            //超期件数
            if (stye == "6")
            {
                var queryOrder = _orderRepository.Queryable()
                         .Where(it => it.CreationTime >= dto.StartTime && it.CreationTime <= dto.EndTime && it.Status > EOrderStatus.WaitForAccept)
                         .WhereIF(dto.OrgCode == "001", it => it.ActualHandleOrgCode == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                         .WhereIF(dto.StatisticsType == EStatisticsType.SubtotalOverdue, it => (it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime))//已办超期
                         .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, it => it.Source == ESource.ProvinceStraight)
                         .Select(it => new SelectOrderId { Id = it.Id })
                         .MergeTable();

                //会签(已办超期、待办超期)
                //var queryCountersign = _workflowStepHandleRepository.Queryable()
                //.LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
                var queryCountersign = _workflowTraceRepository.Queryable()
               .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
               .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && t.CreationTime >= dto.StartTime && t.CreationTime <= dto.EndTime)
               .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
               .WhereIF(dto.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
               .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))

               .WhereIF(dto.StatisticsType == EStatisticsType.SubtotalOverdue, (t, o) => (t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None && t.HandleTime > t.StepExpiredTime)
                        || (t.Status < EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None && DateTime.Now >= t.StepExpiredTime)
                        || (t.Status < EWorkflowStepStatus.Handled && DateTime.Now >= t.StepExpiredTime))//会签已办超期/会签待办超期/待办超期

                .Select((t, o) => new SelectOrderId { Id = o.Id })
               .MergeTable();

                var queryData = _orderRepository.OrderListUnionAll(queryOrder, queryCountersign).GroupBy(x => x.Id).MergeTable();

                return queryData;

            }
            #endregion

            return null;
        }

        /// <summary>
        /// 部门延期统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<BiOrderDelayDataDto> QueryOrderDelayDataList(QueryOrderDelayDataListRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

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

            var IsCenter = _sessionContext.OrgIsCenter;

            return _orderDelayRepository.Queryable()
                .LeftJoin<SystemOrganize>((x, o) => x.ApplyOrgCode == o.Id)
                .LeftJoin<Order>((x, o, s) => x.OrderId == s.Id)
                .WhereIF(dto.StartTime.HasValue, (x, o, s) => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o, s) => x.CreationTime <= dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, (x, o, s) => s.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, (x, o, s) => s.IdentityType == EIdentityType.Enterprise)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.ApplyOrgName.Contains(dto.OrgName))
                .WhereIF(IsCenter == false, x => x.ApplyOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.ApplyOrgCode, x.ApplyOrgName })
                .Select(x => new BiOrderDelayDataDto
                {
                    OrgName = x.ApplyOrgName,
                    OrgCode = x.ApplyOrgCode,
                    PassTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)),
                    NoPassTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.NoPass, 1, 0)),
                    ExaminingTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Examining, 1, 0)),
                    AllTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)) + SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.NoPass, 1, 0)) + SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Examining, 1, 0))

                }).MergeTable();


        }

        /// <summary>
        /// 部门延期统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrderDelay> QueryOrderDelayDataDetail(QueryOrderDelayDataDetailRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

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

            return _orderDelayRepository.Queryable()
                 .Includes(x => x.Order)
                 .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                 .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.Order.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.Order.IdentityType == EIdentityType.Enterprise)
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), x => x.ApplyOrgCode == dto.OrgCode)
                 .WhereIF(dto.Type is 1, x => x.DelayState == EDelayState.Pass)
                 .WhereIF(dto.Type is 2, x => x.DelayState == EDelayState.NoPass)
                 .WhereIF(dto.Type is 3, x => x.DelayState == EDelayState.Examining)
                 .WhereIF(dto.Type is 4, x => x.DelayState < EDelayState.Withdraw)
             .MergeTable();

        }

        /// <summary>
        /// 部门受理类型统计周期
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<DepartmentAcceptanceTypeStatisticsDto> DepartmentAcceptanceTypeStatistics(DepartmentKeyWordRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            var orderData = _orderRepository.Queryable()
              .Where(it => it.CreationTime >= dto.StartTime && it.CreationTime <= dto.EndTime && it.Status >= EOrderStatus.Filed)
            .WhereIF(dto.TypeId != null && dto.TypeId == 1, it => it.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeId != null && dto.TypeId == 2, it => it.IdentityType == EIdentityType.Enterprise)
              .Select(it => new
              {
                  OrgCode = IsCenter == true ? it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : it.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                  AcceptTypeCode = it.AcceptTypeCode,
                  ProcessType = it.ProcessType,
                  AllTime = it.ProcessType == EProcessType.Zhiban ? it.CreationTimeHandleDurationWorkday : it.CenterToOrgHandleDurationWorkday

              })
              .MergeTable()
              .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
              //.WhereIF(TypeCode == 1, (it, o) => it.OrgCode == "001")
              //.WhereIF(TypeCode == 2, (it, o) => it.OrgCode != "001")
              .WhereIF(dto.TypeCode == 1, (it, o) => it.ProcessType == EProcessType.Zhiban)
              .WhereIF(dto.TypeCode == 2, (it, o) => it.ProcessType == EProcessType.Jiaoban)
              .WhereIF(IsCenter == false, (it, o) => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
               .GroupBy((it, o) => new
               {
                   it.OrgCode,
                   o.Name,
                   o.OrgType
               })
                .Select((it, o) => new DepartmentAcceptanceTypeStatisticsDto
                {
                    OrgName = it.OrgCode == "001" ? "市民热线服务中心" : o.Name,
                    OrgCode = it.OrgCode,
                    OrgType = o.OrgType == EOrgType.County ? "区县部门" : "市直部门",
                    ZxAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10", 1, 0)),
                    ZxAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10" && it.AllTime != null, it.AllTime, 0)),
                    ZxAcceptanceTypeCode = "10",

                    JyAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15", 1, 0)),
                    JyAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15" && it.AllTime != null, it.AllTime, 0)),
                    JyAcceptanceTypeCode = "15",

                    QzAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20", 1, 0)),
                    QzAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20" && it.AllTime != null, it.AllTime, 0)),
                    QzAcceptanceTypeCode = "20",

                    ByAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25", 1, 0)),
                    ByAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25" && it.AllTime != null, it.AllTime, 0)),
                    ByAcceptanceTypeCode = "25",

                    JbAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30", 1, 0)),
                    JbAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30" && it.AllTime != null, it.AllTime, 0)),
                    JbAcceptanceTypeCode = "30",

                    TsAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35", 1, 0)),
                    TsAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35" && it.AllTime != null, it.AllTime, 0)),
                    TsAcceptanceTypeCode = "35",

                    QtAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40", 1, 0)),
                    QtAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40" && it.AllTime != null, it.AllTime, 0)),
                    QtAcceptanceTypeCode = "40",

                    YjAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "1", 1, 0)),
                    YjAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "1" && it.AllTime != null, it.AllTime, 0)),
                    YjAcceptanceTypeCode = "1"
                }).MergeTable();
            return orderData;
        }

        /// <summary>
        /// 部门受理类型统计周期--明细列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<Order> DepartmentAcceptanceTypeOrderList(DepartmentKeyWordRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            return _orderRepository.Queryable()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime && p.Status >= EOrderStatus.Filed)
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode == "001", p => p.ActualHandleOrgCode == dto.OrgCode)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001" && _sessionContext.RequiredOrgId != dto.OrgCode, p => p.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001" && _sessionContext.RequiredOrgId == dto.OrgCode, p => p.ActualHandleOrgCode == dto.OrgCode)
                 .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), p => p.AcceptTypeCode == dto.AcceptTypeCode)
                 .WhereIF(dto.TypeCode == 1, p => p.ProcessType == EProcessType.Zhiban)
                 .WhereIF(dto.TypeCode == 2, p => p.ProcessType == EProcessType.Jiaoban)
                 .OrderByDescending(d => d.CreationTime)
                 .MergeTable();
        }

        /// <summary>
        /// 满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrderVisitDetail> OrgVisitDetailList(OrgVisitDetailListReq dto)
        {
            var IsCenter = _sessionContext.OrgIsCenter;

            return _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, x => x.Order, x => x.OrderScreens)
                .Includes(x => x.OrderVisit, x => x.Employee)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited && x.VisitTarget == EVisitTarget.Org)
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .WhereIF(dto.OrgVisitStatisticsType.HasValue, x => x.OrderVisit.Order.ProcessType == (EProcessType)((int)dto.OrgVisitStatisticsType))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgProcessingResults), x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.OrgProcessingResults)
                .WhereIF(!string.IsNullOrEmpty(dto.VisitUser), x => x.OrderVisit.Employee.Name.Contains(dto.VisitUser))
                .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.OrderVisit.Order.No == dto.No)
                .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.OrderVisit.Order.Title.Contains(dto.Title))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgId), x => x.VisitOrgCode == dto.OrgId)//接办部门
                .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), x => x.OrderVisit.Order.HotspotSpliceName != null && x.OrderVisit.Order.HotspotSpliceName.Contains(dto.Hotspot))//热点类型
                .WhereIF(!string.IsNullOrEmpty(dto.Channel), x => x.OrderVisit.Order.SourceChannelCode == dto.Channel)//受理类型
                .WhereIF(dto.CreationTimeStart.HasValue, x => x.OrderVisit.Order.CreationTime >= dto.CreationTimeStart) //受理时间开始
                .WhereIF(dto.CreationTimeEnd.HasValue, x => x.OrderVisit.Order.CreationTime <= dto.CreationTimeEnd) //受理时间结束
                .WhereIF(dto.ActualHandleTimeStart.HasValue, x => x.OrderVisit.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
                .WhereIF(dto.ActualHandleTimeEnd.HasValue, x => x.OrderVisit.Order.ActualHandleTime <= dto.ActualHandleTimeEnd)
                .OrderBy(x => x.OrderVisit.VisitTime); //办结时间结束

        }

        /// <summary>
        /// 受理类型统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<AcceptTypeStatisticsDto> AcceptTypeStatistics(AcceptTypeStatisticsReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var query = _orderRepository.Queryable()
                .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)
                .GroupBy(x => x.AcceptType)
                .Select(x => new AcceptTypeStatisticsDto
                {
                    AcceptType = x.AcceptType,
                    SumCount = SqlFunc.AggregateCount(x.AcceptType),
                    CompletionCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status >= EOrderStatus.Filed, 1, 0)),
                    VisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EOrderStatus.Visited, 1, 0))
                });
            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="isCenter"></param>
        /// <returns></returns>
        public async Task<(IReadOnlyList<SystemDicData> dissatisfiedReason, List<dynamic>? list)> QueryVisitNoSatisfiedAsync(QueryVisitNoSatisfiedDto dto, bool isCenter)
        {
            //var dissatisfiedReason = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.DissatisfiedReason);
            // 保留只需要导出的列 
            var dissatisfiedReason = _sysDicDataCacheManager
                .GetSysDicDataCache(SysDicTypeConsts.DissatisfiedReason);

            if (dto.AddColumnName.Any())
            {
                dissatisfiedReason = dissatisfiedReason
                .Where(m => dto.AddColumnName.Contains(m.DicDataName))
                .ToList();
            }

            List<dynamic>? list = new List<dynamic>();
            //DataTable dt = new DataTable();
            foreach (var item in dissatisfiedReason)
            {
                var table = _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit)
                .Where(x => x.VisitTarget == Share.Enums.Order.EVisitTarget.Org)
                .Where(x => x.OrgNoSatisfiedReason != null)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited)
                .Where(x => !string.IsNullOrEmpty(x.VisitOrgName))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.VisitOrgName.Contains(dto.OrgName))
                .WhereIF(dto.StartTime.HasValue, x => x.OrderVisit.VisitTime >= dto.StartTime.Value)
                .WhereIF(dto.EndTime.HasValue, x => x.OrderVisit.VisitTime <= dto.EndTime.Value)
                .WhereIF(isCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.VisitOrgName, x.VisitOrgCode })
                .Select(x => new BiVisitNoSatisfiedDto
                {
                    Count = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(x.OrgNoSatisfiedReason, "Key", item.DicDataValue), 1, 0)),
                    Key = item.DicDataValue,
                    OrgName = x.VisitOrgName,
                    OrgCode = x.VisitOrgCode
                })
                .OrderByDescending(x => x.Count)
                //.ToPivotTable(x => x.Key, x => x.OrgName, x => x.Sum(x => x.Count));
                .ToPivotList(x => x.Key, x => new { x.OrgCode, x.OrgName }, x => x.Sum(x => x.Count));

                list.AddRange(table);
            }
            return (dissatisfiedReason, list);
        }

        public async Task<DataTable> ExportQueryVisitNoSatisfiedAsync(IReadOnlyList<SystemDicData> dissatisfiedReason, List<dynamic>? list, List<string> addColumnName)
        {
            var dataTable = new DataTable();
            foreach (var item in addColumnName)
            {
                dataTable.Columns.Add(item);
            }

            Dictionary<string, DataRow> dicRow = new();

            // 先拿部门名称
            // bug 部门名称重复时有问题
            // 循环填充 首列 数据
            foreach (var item in list)
            {
                foreach (var property in (IDictionary<string, object>)item)
                {
                    if (property.Key == "OrgName")
                    {
                        var name = property.Value.ToString();
                        if (name.IsNullOrEmpty()) continue;
                        if (dicRow.Any(m => m.Key == name)) continue;
                        var dr = dataTable.NewRow();
                        dr[0] = name;
                        dicRow.Add(name, dr);
                    }
                }
            }

            var drCount = dataTable.NewRow();
            drCount[0] = "合计";
            dicRow.Add("合计", drCount);

            for (int i = 0; i < dissatisfiedReason.Count; i++)
            { // 循环填充列数据

                var total = 0;
                for (int l = 0; l < list.Count; l++)
                {
                    var columnIndex = i + 1;
                    var value = string.Empty;
                    var orgName = string.Empty;
                    foreach (var property in (IDictionary<string, object>)list[l])
                    {
                        if (property.Key.ToLower().Equals("orgname")) orgName = property.Value.ToString();
                        if (property.Key.ToLower().Equals(columnIndex.ToString()))
                        {
                            value = property.Value.ToString();
                            total += int.Parse(value!);
                        }
                    }
                    if (!value.IsNullOrEmpty() && !orgName.IsNullOrEmpty())
                    {
                        dicRow[orgName!][columnIndex] = value;
                    }
                    if (l + 1 == list.Count)
                        dicRow["合计"][columnIndex] = total;
                }
            }
            foreach (var item in dicRow)
            {
                dataTable.Rows.Add(item.Value);
            }
            return dataTable;
        }

        /// <summary>
        /// 未签收统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<List<OrderNoSigenDto>> GetOrderNoSigen(OrderNoSigenRequestDto dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            var isCenter = _sessionContext.OrgIsCenter;
            //工单未超期
            var queryOrderNoSigen = _orderRepository.Queryable()
               .LeftJoin<WorkflowStep>((o, w) => o.Id == w.ExternalId)
               .Where((o, w) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && w.ModuleCode == WorkflowModuleConsts.OrderHandle
                       && w.Status == EWorkflowStepStatus.WaitForAccept && w.CountersignPosition == ECountersignPosition.None)
               .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (o, w) => o.Source == ESource.ProvinceStraight)
               .WhereIF(dto.TypeId != null && dto.TypeId == 1, (o, w) => o.IdentityType == EIdentityType.Citizen)
               .WhereIF(dto.TypeId != null && dto.TypeId == 2, (o, w) => o.IdentityType == EIdentityType.Enterprise)
               .Select((o, w) => new
               {
                   OrgCode = isCenter == true ? w.HandlerOrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : w.HandlerOrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
               })
               .MergeTable()
               .LeftJoin<SystemOrganize>((it, sy) => it.OrgCode == sy.Id)
                // .WhereIF(!string.IsNullOrEmpty(dto.OrgName), (it, sy) => sy.Name.Contains(dto.OrgName))
                .GroupBy((it, sy) => new
                {
                    it.OrgCode,
                    sy.Name,
                })
                 .Select((it, sy) => new OrderNoSigenDto
                 {
                     OrgName = it.OrgCode == "001" ? "市民热线服务中心" : sy.Name,
                     OrgCode = it.OrgCode,
                     OrderNoSigen = SqlFunc.AggregateSum(1),
                     CounterNoSign = 0
                 }).MergeTable();

            //会签未签收
            var queryCounterNoSign = _orderRepository.Queryable()
                .LeftJoin<WorkflowStep>((o, w) => o.Id == w.ExternalId)
                .Where((o, w) => w.CreationTime >= dto.StartTime && w.CreationTime <= dto.EndTime && w.ModuleCode == WorkflowModuleConsts.OrderHandle
                        && w.Status == EWorkflowStepStatus.WaitForAccept && w.CountersignPosition > ECountersignPosition.None)
                .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (o, w) => o.Source == ESource.ProvinceStraight)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, (o, w) => o.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, (o, w) => o.IdentityType == EIdentityType.Enterprise)
                .Select((o, w) => new
                {
                    OrgCode = isCenter == true ? w.HandlerOrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : w.HandlerOrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                })
                .MergeTable()
                .LeftJoin<SystemOrganize>((it, sy) => it.OrgCode == sy.Id)
                 //  .WhereIF(!string.IsNullOrEmpty(dto.OrgName), (it, sy) => sy.Name.Contains(dto.OrgName))
                 .GroupBy((it, sy) => new
                 {
                     it.OrgCode,
                     sy.Name,
                 })
                  .Select((it, sy) => new OrderNoSigenDto
                  {
                      OrgName = it.OrgCode == "001" ? "市民热线服务中心" : sy.Name,
                      OrgCode = it.OrgCode,
                      OrderNoSigen = 0,
                      CounterNoSign = SqlFunc.AggregateSum(1)
                  }).MergeTable();

            return await _orderRepository.UnionAll(queryOrderNoSigen, queryCounterNoSign).GroupBy(p => new { p.OrgCode, p.OrgName })
                .Select(p => new OrderNoSigenDto
                {
                    OrgName = p.OrgName,
                    OrgCode = p.OrgCode,
                    OrderNoSigen = SqlFunc.AggregateSum(p.OrderNoSigen),
                    CounterNoSign = SqlFunc.AggregateSum(p.CounterNoSign),
                })
                .ToListAsync();

        }

        /// <summary>
        /// 未签收统计--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<Order> GetOrderNoSigenDetail(OrderNoSigenRequestDto dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            if (dto.DataSoure == 2)
            {
                //会签未签收
                return _orderRepository.Queryable()
                    .LeftJoin<WorkflowStep>((o, w) => o.Id == w.ExternalId)
                    .Where((o, w) => w.CreationTime >= dto.StartTime && w.CreationTime <= dto.EndTime && w.ModuleCode == WorkflowModuleConsts.OrderHandle
                            && w.Status == EWorkflowStepStatus.WaitForAccept && w.CountersignPosition > ECountersignPosition.None)
                    .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (o, w) => o.Source == ESource.ProvinceStraight)
                    .WhereIF(dto.TypeId != null && dto.TypeId == 1, (o, w) => o.IdentityType == EIdentityType.Citizen)
                    .WhereIF(dto.TypeId != null && dto.TypeId == 2, (o, w) => o.IdentityType == EIdentityType.Enterprise)
                    .WhereIF(dto.OrgCode == "001", (o, w) => w.HandlerOrgId == dto.OrgCode)
                   .WhereIF(dto.OrgCode != "001", (o, w) => w.HandlerOrgId.StartsWith(dto.OrgCode))
                   .MergeTable();
            }
            else
            {
                //工单未超期
                return _orderRepository.Queryable()
                    .LeftJoin<WorkflowStep>((o, w) => o.Id == w.ExternalId)
                    .Where((o, w) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && w.ModuleCode == WorkflowModuleConsts.OrderHandle
                            && w.Status == EWorkflowStepStatus.WaitForAccept && w.CountersignPosition == ECountersignPosition.None)
                    .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (o, w) => o.Source == ESource.ProvinceStraight)
                    .WhereIF(dto.TypeId != null && dto.TypeId == 1, (o, w) => o.IdentityType == EIdentityType.Citizen)
                    .WhereIF(dto.TypeId != null && dto.TypeId == 2, (o, w) => o.IdentityType == EIdentityType.Enterprise)
                    .WhereIF(dto.OrgCode == "001", (o, w) => w.HandlerOrgId == dto.OrgCode)
                    .WhereIF(dto.OrgCode != "001", (o, w) => w.HandlerOrgId.StartsWith(dto.OrgCode))
                  .MergeTable();
            }
        }

        /// <summary>
        /// 企业专席信件统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<List<EnterpriseSeatsReportDto>> GetEnterpriseSeatsReport(EnterpriseSeatsReportRequestDto dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            //查询坐席信息
            var enterpriseSeats = _systemSettingCacheManager.GetSetting(SettingConstants.EnterpriseSeats)?.SettingValue;
            var userData = await _accountRepository.Queryable()
                  .Where(p => enterpriseSeats.Contains(p.UserName)).Select(p => p.Id).ToListAsync();

            //查询工单
            var queryOrderData = _orderRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                .Where(p => userData.Contains(p.SignerId))
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), p => p.SignerName.Contains(dto.UserName))
                .GroupBy(p => new { p.SignerId })
                .Select(p => new EnterpriseSeatsReportDto
                {
                    UserId = p.SignerId,
                    OrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(p.AcceptType != "无效", 1, 0)),
                })
                .MergeTable();

            //查询通话
            var queryCall = _trCallRecordRepository.Queryable()
                .Where(t => t.CreatedTime >= dto.StartTime && t.CreatedTime <= dto.EndTime)
                .Where(t => userData.Contains(t.UserId))
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), t => t.UserName.Contains(dto.UserName))
                .GroupBy(t => new { t.UserId })
                .Select(t => new EnterpriseSeatsReportDto
                {
                    UserId = t.UserId,
                    TelCallNum = SqlFunc.AggregateSum(SqlFunc.IIF(t.CallDirection == ECallDirection.In || t.CallDirection == ECallDirection.Out, 1, 0)),
                })
                .MergeTable();

            return await _userRepository.Queryable()
                .Where(u => userData.Contains(u.Id))
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), u => u.Name.Contains(dto.UserName))
                .LeftJoin(queryOrderData, (u, qo) => u.Id == qo.UserId)
                .LeftJoin(queryCall, (u, qo, qc) => u.Id == qc.UserId)
                .Select((u, qo, qc) => new EnterpriseSeatsReportDto
                {
                    UserName = u.Name,
                    UserId = u.Id,
                    UserNo = u.StaffNo,
                    TelCallNum = qc.TelCallNum,
                    OrderNum = qo.OrderNum,
                })
                .ToListAsync();

        }

        /// <summary>
        /// 企业专席信件统计--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<Order> GetEnterpriseSeatsReportDetail(EnterpriseSeatsReportRequestDto dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            return _orderRepository.Queryable()
                  .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.SignerId == dto.UserId)
                  .WhereIF(!string.IsNullOrEmpty(dto.UserName), o => o.SignerName.Contains(dto.UserName))
               .OrderByDescending(o => o.CreationTime);

        }
    }
}