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

using Hotline.Application.Orders;
using Hotline.Caching.Interfaces;
using Hotline.CallCenter.Calls;
using Hotline.FlowEngine.WorkflowModules;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Settings.TimeLimits;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using MapsterMapper;
using SqlSugar;
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 IRepository<Hotspot> _hotspotTypeRepository;
        private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<OrderDelay> _orderDelayRepository;
        private readonly IMapper _mapper;
        private readonly IRepository<WorkflowCountersign> _workflowCountersignRepository;
        private readonly IRepository<OrderSpecial> _orderSpecialRepository;
        private readonly IRepository<OrderVisit> _orderVisitRepository;
        private readonly IRepository<TrCallRecord> _trCallRecordRepository;
        private readonly IRepository<OrderPublish> _orderPublishRepository;
        private readonly IRepository<SystemOrganize> _systemOrganizeRepository;
        private readonly IRepository<AiOrderVisitDetail> _aiOrderVisitDetailRepository;
        private readonly ISessionContext _sessionContext;
        private readonly ISystemSettingCacheManager _systemSettingCacheManager;
        private readonly IRepository<OrderSpecialDetail> _orderSpecialDetailRepository;
        private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
        private readonly IRepository<OrderScreen> _orderScreenRepository;
        private readonly IOrderSecondaryHandlingApplication _orderSecondaryHandlingApplication;
        private readonly ITimeLimitDomainService _timeLimitDomainService;

        public OrderReportApplication(
            IOrderRepository orderRepository,
            IRepository<Hotspot> hotspotTypeRepository,
            ISystemDicDataCacheManager sysDicDataCacheManager,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            IRepository<OrderDelay> orderDelayRepository,
            IRepository<WorkflowCountersign> workflowCountersignRepository,
            IRepository<OrderSpecial> orderSpecialRepository,
            IMapper mapper,
            IRepository<OrderVisit> orderVisitRepository,
            IRepository<TrCallRecord> trCallRecordRepository,
            IRepository<OrderPublish> orderPublishRepository,
            IRepository<SystemOrganize> systemOrganizeRepository,
            IRepository<AiOrderVisitDetail> aiOrderVisitDetailRepository,
            ISessionContext sessionContext,
            ISystemSettingCacheManager systemSettingCacheManager,
            IRepository<OrderSpecialDetail> orderSpecialDetailRepository,
            IRepository<WorkflowTrace> workflowTraceRepository,
            IRepository<OrderScreen> orderScreenRepository,
            IOrderSecondaryHandlingApplication orderSecondaryHandlingApplication,
            ITimeLimitDomainService timeLimitDomainService
            )
        {
            _orderRepository = orderRepository;
            _hotspotTypeRepository = hotspotTypeRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _workflowCountersignRepository = workflowCountersignRepository;
            _orderSpecialRepository = orderSpecialRepository;
            _mapper = mapper;
            _orderVisitRepository = orderVisitRepository;
            _trCallRecordRepository = trCallRecordRepository;
            _orderPublishRepository = orderPublishRepository;
            _systemOrganizeRepository = systemOrganizeRepository;
            _aiOrderVisitDetailRepository = aiOrderVisitDetailRepository;
            _sessionContext = sessionContext;
            _systemSettingCacheManager = systemSettingCacheManager;
            _orderSpecialDetailRepository = orderSpecialDetailRepository;
            _workflowTraceRepository = workflowTraceRepository;
            _orderScreenRepository = orderScreenRepository;
            _orderSecondaryHandlingApplication = orderSecondaryHandlingApplication;
            _timeLimitDomainService = timeLimitDomainService;
        }
        /// <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""
, ""PublishedNoOpen""
,""YbOverdue""
, ""ZbOverdue""
, ""HqzbOverdue""
, ""HqybOverdue""
, ""DelayEnd""
, ""DelayWait""
, ""OrderDelayCount""
, ""ScreenCount""
, ""ScreenApproval""
, ""ScreenPass""
,""ScreenNotPass""
,""SatisfactionCount""
,""NotSatisfactionCount""
, (""YbOrderCountNum""-""YbOverdue""  -""HqybOverdue"" ) ""CompleteOnTime""

from (

SELECT ""HandlerOrgId"" ""OrgCode""
,SUM(""YbOrderCountNum"") ""YbOrderCountNum""
,SUM(""ZbOrderCountNum"") ""ZbOrderCountNum""
,SUM(""Archived"") ""Archived""
,SUM(""ToBeArchived"") ""ToBeArchived""
,SUM(""WaitPublished"") ""WaitPublished""
,SUM(""PublishedOpen"") ""PublishedOpen""
,SUM(""PublishedNoOpen"") ""PublishedNoOpen""
,SUM(""YbOverdue"") ""YbOverdue""
,SUM(""ZbOverdue"") ""ZbOverdue""
,SUM(""HqzbOverdue"") ""HqzbOverdue""
,SUM(""HqybOverdue"") ""HqybOverdue""
,SUM(""DelayEnd"") ""DelayEnd""
,SUM(""DelayWait"") ""DelayWait""
,SUM(""OrderDelayCount"") ""OrderDelayCount""
,SUM(""ScreenCount"") ""ScreenCount""
,SUM(""ScreenApproval"") ""ScreenApproval""
,SUM(""ScreenPass"") ""ScreenPass""
,SUM(""ScreenNotPass"") ""ScreenNotPass""
,SUM(""SatisfactionCount"") ""SatisfactionCount""
,SUM(""NotSatisfactionCount"") ""NotSatisfactionCount""
FROM (

		/*查询已办*/
		select  ""HandlerOrgId"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10  and ""t"".""Status"">=2   {2} --and ""t"".""CountersignPosition""<=1 
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询待办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2  {2} --and ""t"".""CountersignPosition""<=1 
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询已办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""HandleTime"">""StepExpiredTime"" and ""t"".""CountersignPosition""=0   {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询待办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and (SELECT NOW())>""StepExpiredTime"" and ""t"".""CountersignPosition""=0   {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签已办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""HandleTime"">""StepExpiredTime"" and ""CountersignPosition"">0  {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签待办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS  ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and (SELECT NOW())>""StepExpiredTime"" 
			and   ""CountersignPosition"">0  {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签已办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""CountersignPosition"">0 {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签待办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",0 ""DelayEnd"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS  ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and ""CountersignPosition"">0 {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		UNION ALL
		/*已归档、 待发布、已发布公开、已发布不公开*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",0 ""DelayEnd"",0 ""DelayWait""
		,SUM(( CASE  WHEN ( ""Status"">=300 ) THEN 1  ELSE 0 END )) AS ""Archived""
		,0 ""ToBeArchived""
		,SUM(( CASE  WHEN ( ""Status""=300 ) THEN 1  ELSE 0 END )) AS ""WaitPublished""
		,SUM(( CASE  WHEN ( ""Status"">=400 and ""IsPublicity""=TRUE  ) THEN 1  ELSE 0 END )) AS ""PublishedOpen""
		,SUM(( CASE  WHEN ( ""Status"">=400 and ""IsPublicity""=FALSE  ) THEN 1  ELSE 0 END )) AS ""PublishedNoOpen""
		,0 ""OrderDelayCount"",0 ""ScreenCount"",0 ""ScreenApproval"", 0 ""ScreenPass"" , 0 ""ScreenNotPass"" ,0 ""SatisfactionCount"", 0 ""NotSatisfactionCount"" from(
			select  SUBSTRING(""o"".""ActualHandleOrgCode"",1,{3}) ""HandlerOrgId"", ""o"".""Status"", ""o"".""IsPublicity"" from ""order"" ""o""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and  ""o"".""Status"">=300  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询延期通过数量*/
		select  ""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 )) AS  ""OrderDelayCount"",0 ""ScreenCount"",0 ""ScreenApproval"", 0 ""ScreenPass"" , 0 ""ScreenNotPass"" ,0 ""SatisfactionCount"", 0 ""NotSatisfactionCount"" from(
			select  SUBSTRING(""ApplyOrgCode"",1,{3}) ""HandlerOrgId"" from ""order_delay"" ""d""
			left join ""order"" ""o"" on ""d"".""OrderId""=""o"".""Id""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}' and ""d"".""DelayState""=1  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union all 
		/*查询甄别*/
		select  ""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"" 
		,SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""ScreenCount""
		,SUM(( CASE  WHEN ( ""Status""<=1 ) THEN 1  ELSE 0 END )) AS ""ScreenApproval""
		,SUM(( CASE  WHEN ( ""Status""=2 ) THEN 1  ELSE 0 END )) AS ""ScreenPass""
		,SUM(( CASE  WHEN ( ""Status""=3 ) THEN 1  ELSE 0 END )) AS ""ScreenNotPass"",0 ""SatisfactionCount"", 0 ""NotSatisfactionCount""
		from(
			select  SUBSTRING(""d"".""CreatorOrgId"",1,{3}) ""HandlerOrgId"",""d"".""Status"" from ""order_screen"" ""d""
			left join ""order"" ""o"" on ""d"".""OrderId""=""o"".""Id""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询满意度*/
		select  ""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"" 
		,SUM(( CASE  WHEN ( (""OrgProcessingResults""->>'Key')::VARCHAR != '2' ) THEN 1  ELSE 0 END )) AS ""SatisfactionCount""
		,SUM(( CASE  WHEN ( (""OrgProcessingResults""->>'Key')::VARCHAR = '2'  ) THEN 1  ELSE 0 END )) AS ""NotSatisfactionCount""
		FROM(
			SELECT SUBSTRING(""VisitOrgCode"",1,{3}) ""HandlerOrgId"" ,""OrgProcessingResults"" from ""order_visit_detail"" ""d""
			left join ""order_visit"" ""v"" on ""v"".""Id""=""d"".""VisitId""
			left join ""order"" ""o"" on ""o"".""Id""=""v"".""OrderId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}' and ""d"".""VisitTarget""=20 and ""v"".""VisitState""=30 and ""d"".""VisitOrgCode"" is not NULL  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
)""te""
GROUP BY ""HandlerOrgId""
) ""su""
left join ""system_organize"" ""so"" on ""so"".""Id""=""su"".""OrgCode""
where 1=1 {5}
order by ""su"".""OrgCode""";
            var orgLength = IsCenter == true ? 6 : _sessionContext.RequiredOrgId.Length + 3;
            var isOrg = IsCenter == false ? " and \"HandlerOrgId\" like concat('" + _sessionContext.RequiredOrgId + "','%') " : " ";
            var IsProvince = dto.IsProvince.HasValue && dto.IsProvince == true ? "  and \"o\".\"Source\"=100 " : " ";
            var orgName = string.IsNullOrEmpty(dto.OrgName) == false ? "   and   \"OrgName\" like concat('%'," + dto.OrgName + ",'%')" : "";

            strSql = string.Format(strSql, dto.StartTime, dto.EndTime, IsProvince, 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""
, ""PublishedNoOpen""
,""YbOverdue""
, ""ZbOverdue""
, ""HqzbOverdue""
, ""HqybOverdue""
, ""DelayEnd""
, ""DelayWait""
, ""OrderDelayCount""
, ""ScreenCount""
, ""ScreenApproval""
, ""ScreenPass""
,""ScreenNotPass""
,""SatisfactionCount""
,""NotSatisfactionCount""
, (""YbOrderCountNum""-""YbOverdue""  -""HqybOverdue"" ) ""CompleteOnTime""

from (

SELECT ""HandlerOrgId"" ""OrgCode""
,SUM(""YbOrderCountNum"") ""YbOrderCountNum""
,SUM(""ZbOrderCountNum"") ""ZbOrderCountNum""
,SUM(""Archived"") ""Archived""
,SUM(""ToBeArchived"") ""ToBeArchived""
,SUM(""WaitPublished"") ""WaitPublished""
,SUM(""PublishedOpen"") ""PublishedOpen""
,SUM(""PublishedNoOpen"") ""PublishedNoOpen""
,SUM(""YbOverdue"") ""YbOverdue""
,SUM(""ZbOverdue"") ""ZbOverdue""
,SUM(""HqzbOverdue"") ""HqzbOverdue""
,SUM(""HqybOverdue"") ""HqybOverdue""
,SUM(""DelayEnd"") ""DelayEnd""
,SUM(""DelayWait"") ""DelayWait""
,SUM(""OrderDelayCount"") ""OrderDelayCount""
,SUM(""ScreenCount"") ""ScreenCount""
,SUM(""ScreenApproval"") ""ScreenApproval""
,SUM(""ScreenPass"") ""ScreenPass""
,SUM(""ScreenNotPass"") ""ScreenNotPass""
,SUM(""SatisfactionCount"") ""SatisfactionCount""
,SUM(""NotSatisfactionCount"") ""NotSatisfactionCount""
FROM (

		/*查询已办*/
		select  ""HandlerOrgId"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2   {2} --and ""t"".""CountersignPosition""<=1 
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询待办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2  {2} --and ""t"".""CountersignPosition""<=1 
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询已办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""HandleTime"">""StepExpiredTime"" and ""t"".""CountersignPosition""=0   {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询待办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and (SELECT NOW())>""StepExpiredTime"" and ""t"".""CountersignPosition""=0   {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签已办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""HandleTime"">""StepExpiredTime"" and ""CountersignPosition"">0  {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签待办超期*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS  ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and (SELECT NOW())>""StepExpiredTime"" 
			and   ""CountersignPosition"">0  {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签已办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""TraceState"" !=10 and ""t"".""Status"">=2 and ""CountersignPosition"">0 {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询会签待办*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",0 ""DelayEnd"",SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS  ""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"" from(
		 select ""o"".""Id"",SUBSTRING(""t"".""HandlerOrgId"",1,{3}) ""HandlerOrgId"" from ""workflow_trace"" ""t""
			left join ""order"" ""o"" on ""o"".""Id""=""t"".""ExternalId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and ""t"".""ModuleCode""='OrderHandle' and ""t"".""Status""<2 and ""CountersignPosition"">0 {2}
			GROUP BY SUBSTRING(""t"".""HandlerOrgId"",1,{3}),""o"".""Id""
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		UNION ALL
		/*已归档、 待发布、已发布公开、已发布不公开*/
		select  ""HandlerOrgId"",0 ""YbOrderCountNum"",0 ""ZbOrderCountNum"",0 ""YbOverdue"" ,0 ""ZbOverdue"",0 ""HqybOverdue"",0 ""HqzbOverdue"",0 ""DelayEnd"",0 ""DelayWait""
		,SUM(( CASE  WHEN ( ""Status"">=300 ) THEN 1  ELSE 0 END )) AS ""Archived""
		,0 ""ToBeArchived""
		,SUM(( CASE  WHEN ( ""Status""=300 ) THEN 1  ELSE 0 END )) AS ""WaitPublished""
		,SUM(( CASE  WHEN ( ""Status"">=400 and ""IsPublicity""=TRUE  ) THEN 1  ELSE 0 END )) AS ""PublishedOpen""
		,SUM(( CASE  WHEN ( ""Status"">=400 and ""IsPublicity""=FALSE  ) THEN 1  ELSE 0 END )) AS ""PublishedNoOpen""
		,0 ""OrderDelayCount"",0 ""ScreenCount"",0 ""ScreenApproval"", 0 ""ScreenPass"" , 0 ""ScreenNotPass"" ,0 ""SatisfactionCount"", 0 ""NotSatisfactionCount"" from(
			select  SUBSTRING(""o"".""ActualHandleOrgCode"",1,{3}) ""HandlerOrgId"", ""o"".""Status"", ""o"".""IsPublicity"" from ""order"" ""o""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  and  ""o"".""Status"">=300  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询延期通过数量*/
		select  ""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 )) AS  ""OrderDelayCount"",0 ""ScreenCount"",0 ""ScreenApproval"", 0 ""ScreenPass"" , 0 ""ScreenNotPass"" ,0 ""SatisfactionCount"", 0 ""NotSatisfactionCount"" from(
			select  SUBSTRING(""ApplyOrgCode"",1,{3}) ""HandlerOrgId"" from ""order_delay"" ""d""
			left join ""order"" ""o"" on ""d"".""OrderId""=""o"".""Id""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}' and ""d"".""DelayState""=1  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union all 
		/*查询甄别*/
		select  ""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"" 
		,SUM(( CASE  WHEN ( ""HandlerOrgId"" IS NOT NULL ) THEN 1  ELSE 0 END )) AS ""ScreenCount""
		,SUM(( CASE  WHEN ( ""Status""<=1 ) THEN 1  ELSE 0 END )) AS ""ScreenApproval""
		,SUM(( CASE  WHEN ( ""Status""=2 ) THEN 1  ELSE 0 END )) AS ""ScreenPass""
		,SUM(( CASE  WHEN ( ""Status""=3 ) THEN 1  ELSE 0 END )) AS ""ScreenNotPass"",0 ""SatisfactionCount"", 0 ""NotSatisfactionCount""
		from(
			select  SUBSTRING(""d"".""CreatorOrgId"",1,{3}) ""HandlerOrgId"",""d"".""Status"" from ""order_screen"" ""d""
			left join ""order"" ""o"" on ""d"".""OrderId""=""o"".""Id""
			where  ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}'  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL  {4}
		GROUP BY ""HandlerOrgId""
		union ALL
		/*查询满意度*/
		select  ""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"" 
		,SUM(( CASE  WHEN ( (""OrgProcessingResults""->>'Key')::VARCHAR != '2' ) THEN 1  ELSE 0 END )) AS ""SatisfactionCount""
		,SUM(( CASE  WHEN ( (""OrgProcessingResults""->>'Key')::VARCHAR = '2'  ) THEN 1  ELSE 0 END )) AS ""NotSatisfactionCount""
		FROM(
			SELECT SUBSTRING(""VisitOrgCode"",1,{3}) ""HandlerOrgId"" ,""OrgProcessingResults"" from ""order_visit_detail"" ""d""
			left join ""order_visit"" ""v"" on ""v"".""Id""=""d"".""VisitId""
			left join ""order"" ""o"" on ""o"".""Id""=""v"".""OrderId""
			where ""o"".""CreationTime"">='{0}' and ""o"".""CreationTime""<='{1}' and ""d"".""VisitTarget""=20 and ""v"".""VisitState""=30 and ""d"".""VisitOrgCode"" is not NULL  {2}
		)""s"" where ""HandlerOrgId"" IS not NULL {4}
		GROUP BY ""HandlerOrgId""
)""te""
GROUP BY ""HandlerOrgId""
) ""su""
left join ""system_organize"" ""so"" on ""so"".""Id""=""su"".""OrgCode""
where 1=1 {5}
order by ""su"".""OrgCode""";

            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   \"OrgName\" like concat('%'," + dto.OrgName + ",'%')" : "";

            strSql = string.Format(strSql, dto.StartTime, dto.EndTime, IsProvince, 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://工单已办
                    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
                         && t.Status >= EWorkflowStepStatus.Handled &&t.TraceState!= EWorkflowTraceState.StepRemoveByPrevious)
                         .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))
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.ZBOrderCountNum://工单在办
                    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
                       && t.Status < EWorkflowStepStatus.Handled)
                       .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))
                       .GroupBy((t, o) => o.Id)
                       .Select((t, o) => new SelectOrderId { Id = o.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.OrderDelayCount://延期次数
                    query = _orderDelayRepository.Queryable()
                        .Includes(x => x.Order)
                        .Where(x => x.Order.CreationTime >= dto.StartTime && x.Order.CreationTime <= dto.EndTime && x.DelayState == EDelayState.Pass)
                        .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, x => x.Order.Source == ESource.ProvinceStraight)
                        .WhereIF(dto.OrgCode == "001", x => x.ApplyOrgCode == dto.OrgCode)
                        .WhereIF(dto.OrgCode != "001", x => x.ApplyOrgCode.StartsWith(dto.OrgCode))
                        .OrderByDescending(x => x.Order.CreationTime)
                        .Select(x => new SelectOrderId { Id = x.Order.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.YBOverdue://已办超期
                    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
                         && 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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.ZBOverdue://待办超期
                    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
                         && 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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .GroupBy((t, o) => o.Id)
                         .Select((t, 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 && o.CreationTime >= dto.StartTime && o.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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(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 && o.CreationTime >= dto.StartTime && o.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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                         .GroupBy((t, o) => o.Id)
                         .Select((t, o) => new SelectOrderId { Id = o.Id })
                         .MergeTable();
                    break;
                case EStatisticsType.SubtotalOverdue://超期件数
                    var cqybquery = _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
                            && 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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                            .GroupBy((t, o) => o.Id)
                            .Select((t, o) => new SelectOrderId { Id = o.Id })
                            .MergeTable();
                    var cqdbquery = _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
                             && 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.OrgCode == "001", (t, o) => t.HandlerOrgId == dto.OrgCode)
                             .WhereIF(dto.OrgCode != "001", (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
                             .GroupBy((t, o) => o.Id)
                             .Select((t, o) => new SelectOrderId { Id = o.Id })
                             .MergeTable();
                    query = _orderRepository.UnionAll(cqybquery, cqdbquery).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 && o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime
                            && t.Status >= EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None && t.TraceState != EWorkflowTraceState.StepRemoveByPrevious)
                            .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))
                            .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 && o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime
                             && t.Status < EWorkflowStepStatus.Handled && t.CountersignPosition > ECountersignPosition.None)
                             .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))
                             .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.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                            .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(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.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                          .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(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.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(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.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(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.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
                        .WhereIF(dto.OrgCode != "001", o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                         .Select(o => new SelectOrderId { Id = o.Id })
                      .MergeTable();
                    break;
                case EStatisticsType.ScreenCount://甄别总量
                    query = _orderScreenRepository.Queryable()
                        .Includes(x => x.Order)
                       .Where(x => x.Order.CreationTime >= dto.StartTime && x.Order.CreationTime <= dto.EndTime && x.Order.Id != null)
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, x => x.Order.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.OrgCode == "001", x => x.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001", x => x.CreatorOrgId.StartsWith(dto.OrgCode))
                        .Select(x => new SelectOrderId { Id = x.Order.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenApproval://带甄别
                    query = _orderScreenRepository.Queryable()
                        .Includes(x => x.Order)
                       .Where(x => x.Order.CreationTime >= dto.StartTime && x.Order.CreationTime <= dto.EndTime && x.Order.Id != null
                       && (x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply))
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, x => x.Order.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.OrgCode == "001", x => x.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001", x => x.CreatorOrgId.StartsWith(dto.OrgCode))
                        .Select(x => new SelectOrderId { Id = x.Order.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenPass://甄别通过
                    query = _orderScreenRepository.Queryable()
                        .Includes(x => x.Order)
                       .Where(x => x.Order.CreationTime >= dto.StartTime && x.Order.CreationTime <= dto.EndTime && x.Order.Id != null && x.Status == EScreenStatus.End)
                       .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, x => x.Order.Source == ESource.ProvinceStraight)
                       .WhereIF(dto.OrgCode == "001", x => x.CreatorOrgId == dto.OrgCode)
                       .WhereIF(dto.OrgCode != "001", x => x.CreatorOrgId.StartsWith(dto.OrgCode))
                        .Select(x => new SelectOrderId { Id = x.Order.Id })
                       .MergeTable();
                    break;
                case EStatisticsType.ScreenNotPass://甄别不通过
                    query = _orderScreenRepository.Queryable()
                       .Includes(x => x.Order)
                      .Where(x => x.Order.CreationTime >= dto.StartTime && x.Order.CreationTime <= dto.EndTime && x.Order.Id != null && x.Status == EScreenStatus.Refuse)
                      .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, x => x.Order.Source == ESource.ProvinceStraight)
                      .WhereIF(dto.OrgCode == "001", x => x.CreatorOrgId == dto.OrgCode)
                      .WhereIF(dto.OrgCode != "001", x => x.CreatorOrgId.StartsWith(dto.OrgCode))
                       .Select(x => new SelectOrderId { Id = x.Order.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(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();

            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)
                .WhereIF(dto.StartTime.HasValue, (x, o) => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o) => x.CreationTime <= dto.EndTime)
                .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(!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)
              .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(!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();
        }
    }
}