123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854 |
- 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<OrderVisitDetail> _orderVisitDetailRepository;
- private readonly IRepository<OrderDelay> _orderDelayRepository;
- private readonly IMapper _mapper;
- private readonly IRepository<OrderPublish> _orderPublishRepository;
- private readonly ISessionContext _sessionContext;
- private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
- private readonly IRepository<OrderScreen> _orderScreenRepository;
- private readonly IOrderSecondaryHandlingApplication _orderSecondaryHandlingApplication;
- private readonly ITimeLimitDomainService _timeLimitDomainService;
- private readonly IRepository<SystemDicData> _systemDicDataRepository;
- /// <summary>
- ///
- /// </summary>
- /// <param name="orderRepository"></param>
- /// <param name="orderVisitDetailRepository"></param>
- /// <param name="orderDelayRepository"></param>
- /// <param name="mapper"></param>
- /// <param name="orderPublishRepository"></param>
- /// <param name="sessionContext"></param>
- /// <param name="workflowTraceRepository"></param>
- /// <param name="orderScreenRepository"></param>
- /// <param name="orderSecondaryHandlingApplication"></param>
- /// <param name="timeLimitDomainService"></param>
- public OrderReportApplication(
- IOrderRepository orderRepository,
- IRepository<OrderVisitDetail> orderVisitDetailRepository,
- IRepository<OrderDelay> orderDelayRepository,
- IMapper mapper,
- IRepository<OrderPublish> orderPublishRepository,
- ISessionContext sessionContext,
- IRepository<WorkflowTrace> workflowTraceRepository,
- IRepository<OrderScreen> orderScreenRepository,
- IOrderSecondaryHandlingApplication orderSecondaryHandlingApplication,
- ITimeLimitDomainService timeLimitDomainService,
- IRepository<SystemDicData> systemDicDataRepository
- )
- {
- _orderRepository = orderRepository;
- _orderVisitDetailRepository = orderVisitDetailRepository;
- _orderDelayRepository = orderDelayRepository;
- _mapper = mapper;
- _orderPublishRepository = orderPublishRepository;
- _sessionContext = sessionContext;
- _workflowTraceRepository = workflowTraceRepository;
- _orderScreenRepository = orderScreenRepository;
- _orderSecondaryHandlingApplication = orderSecondaryHandlingApplication;
- _timeLimitDomainService = timeLimitDomainService;
- _systemDicDataRepository = systemDicDataRepository;
- }
- /// <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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.OrderDelayCount://延期次数
- query = _orderDelayRepository.Queryable()
- .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
- .Where((d, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && d.DelayState == EDelayState.Pass)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", (d, o) => d.ApplyOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.ApplyOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode == "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.ApplyOrgCode == dto.OrgCode)
- .OrderByDescending((d, o) => o.CreationTime)
- .Select((d, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.YBOverdue://已办超期
- query = _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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.HQZBOverdue://会签待办超期
- query = _workflowTraceRepository.Queryable()
- .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
- .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && 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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.SubtotalOverdue://超期件数
- var 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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- var 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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- query = _orderRepository.UnionAll(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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.DelayWait://会签待办
- query = _workflowTraceRepository.Queryable()
- .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
- .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && 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" && dto.OrgCode != dto.ParentOrgCode, (t, o) => t.HandlerOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (t, o) => t.HandlerOrgId == dto.OrgCode)
- .GroupBy((t, o) => o.Id)
- .Select((t, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.ToBeArchived://待归档没得数据
- query = _orderRepository.Queryable()
- .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status < EOrderStatus.WaitForAccept)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
- .Select(o => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.Archived://已归档
- query = _orderRepository.Queryable()
- .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Filed)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
- .Select(o => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.WaitPublished://待发布
- query = _orderRepository.Queryable()
- .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status == EOrderStatus.Filed)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
- .Select(o => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.PublishedOpen://发布公开
- query = _orderRepository.Queryable()
- .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Published && o.IsPublicity == true)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
- .Select(o => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.PublishedNoOpen://发布不公开
- query = _orderRepository.Queryable()
- .Where(o => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Status >= EOrderStatus.Published && o.IsPublicity == false)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, o => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", o => o.ActualHandleOrgCode == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, o => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, o => o.ActualHandleOrgCode == dto.OrgCode)
- .Select(o => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.ScreenCount://甄别总量
- query = _orderScreenRepository.Queryable()
- .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
- .Where((d, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Id != null)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
- .Select((d, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.ScreenApproval://带甄别
- query = _orderScreenRepository.Queryable()
- .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
- .Where((d, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Id != null
- && (d.Status == EScreenStatus.Approval || d.Status == EScreenStatus.Apply))
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
- .Select((d, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.ScreenPass://甄别通过
- query = _orderScreenRepository.Queryable()
- .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
- .Where((d, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Id != null && d.Status == EScreenStatus.End)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
- .Select((d, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- case EStatisticsType.ScreenNotPass://甄别不通过
- query = _orderScreenRepository.Queryable()
- .LeftJoin<Order>((d, o) => d.OrderId == o.Id)
- .Where((d, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Id != null && d.Status == EScreenStatus.Refuse)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (d, o) => o.Source == ESource.ProvinceStraight)
- .WhereIF(dto.OrgCode == "001", (d, o) => d.CreatorOrgId == dto.OrgCode)
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode != dto.ParentOrgCode, (d, o) => d.CreatorOrgId.StartsWith(dto.OrgCode))
- .WhereIF(dto.OrgCode != "001" && dto.OrgCode == dto.ParentOrgCode, (d, o) => d.CreatorOrgId == dto.OrgCode)
- .Select((d, o) => new SelectOrderId { Id = o.Id })
- .MergeTable();
- break;
- default:
- break;
- }
- return query;
- }
- /// <summary>
- /// 部门办件统计明细表
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- public ISugarQueryable<SelectOrderId> DepartmentalProcessingStatisticsDetailsList(DepartmentalProcessingStatisticsRequest dto)
- {
- var query = _workflowTraceRepository.Queryable()
- .LeftJoin<Order>((t, o) => t.ExternalId == o.Id)
- .Where((t, o) => t.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime)
- .WhereIF(dto.IsProvince.HasValue && dto.IsProvince == true, (t, o) => o.Source == ESource.ProvinceStraight)
- // .WhereIF(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();
- }
- /// <summary>
- /// 满意度明细
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- public ISugarQueryable<OrderVisitDetail> OrgVisitDetailList(OrgVisitDetailListReq dto)
- {
- var IsCenter = _sessionContext.OrgIsCenter;
- return _orderVisitDetailRepository.Queryable()
- .Includes(x => x.OrderVisit, x => x.Order, x => x.OrderScreens)
- .Includes(x => x.OrderVisit, x => x.Employee)
- .Where(x => x.OrderVisit.VisitState == EVisitState.Visited && x.VisitTarget == EVisitTarget.Org)
- .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
- .WhereIF(dto.OrgVisitStatisticsType.HasValue, x => x.OrderVisit.Order.ProcessType == (EProcessType)((int)dto.OrgVisitStatisticsType))
- .WhereIF(!string.IsNullOrEmpty(dto.OrgProcessingResults), x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.OrgProcessingResults)
- .WhereIF(!string.IsNullOrEmpty(dto.VisitUser), x => x.OrderVisit.Employee.Name.Contains(dto.VisitUser))
- .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.OrderVisit.Order.No == dto.No)
- .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.OrderVisit.Order.Title.Contains(dto.Title))
- .WhereIF(!string.IsNullOrEmpty(dto.OrgId), x => x.VisitOrgCode == dto.OrgId)//接办部门
- .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), x => x.OrderVisit.Order.HotspotSpliceName != null && x.OrderVisit.Order.HotspotSpliceName.Contains(dto.Hotspot))//热点类型
- .WhereIF(!string.IsNullOrEmpty(dto.Channel), x => x.OrderVisit.Order.SourceChannelCode == dto.Channel)//受理类型
- .WhereIF(dto.CreationTimeStart.HasValue, x => x.OrderVisit.Order.CreationTime >= dto.CreationTimeStart) //受理时间开始
- .WhereIF(dto.CreationTimeEnd.HasValue, x => x.OrderVisit.Order.CreationTime <= dto.CreationTimeEnd) //受理时间结束
- .WhereIF(dto.ActualHandleTimeStart.HasValue, x => x.OrderVisit.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
- .WhereIF(dto.ActualHandleTimeEnd.HasValue, x => x.OrderVisit.Order.ActualHandleTime <= dto.ActualHandleTimeEnd)
- .OrderBy(x => x.OrderVisit.VisitTime); //办结时间结束
- }
- /// <summary>
- /// 受理类型统计
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- public ISugarQueryable<AcceptTypeStatisticsDto> AcceptTypeStatistics(AcceptTypeStatisticsReq dto)
- {
- dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
- var query = _orderRepository.Queryable()
- .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
- .WhereIF(dto.TypeId !=null && dto.TypeId == 1,x=>x.IdentityType == EIdentityType.Citizen)
- .WhereIF(dto.TypeId != null && dto.TypeId == 2,x=>x.IdentityType == EIdentityType.Enterprise)
- .WhereIF(!string.IsNullOrEmpty(dto.Gateway),x=>x.TransferPhone == dto.Gateway)
- .GroupBy(x => x.AcceptType)
- .Select(x => new AcceptTypeStatisticsDto
- {
- AcceptType = x.AcceptType,
- SumCount = SqlFunc.AggregateCount(x.AcceptType),
- CompletionCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status >= EOrderStatus.Filed, 1, 0)),
- VisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EOrderStatus.Visited, 1, 0))
- });
- return query;
- }
- }
- }
|