hotline/src/Hotline/dataview.md

### 信件数据查询 select aaa.*,bbb."SeatVisitResult",ccc."OrgProcessingResults" from (select CASE "ordertemp"."Status"

WHEN 500 THEN '已回访'
WHEN 400 THEN '已发布'
WHEN 300 THEN '已归档'
WHEN 200 THEN '会签中'
WHEN 105 THEN '退回审批中'
WHEN 104 THEN '特提审批中'
WHEN 103 THEN '移交信件'
WHEN 102 THEN '退回信件'
WHEN 101 THEN '特提信件'
WHEN 100 THEN '办理中'
WHEN 9 THEN '退回省平台'
WHEN 2 THEN '特提待受理'
WHEN 1 THEN '退回待受理'
WHEN 0 THEN '待受理'
ELSE    '未知'

END AS "Status", CASE WHEN "Status">=300 and "ExpiredTime">"FiledTime" THEN '正常'

      WHEN "Status">=300 AND "ExpiredTime"<"FiledTime" THEN '超期'
        WHEN "Status"<300 AND "ExpiredTime"> now() AND now()> "NearlyExpiredTime" THEN '即将超期'
        WHEN "Status"<300 AND "ExpiredTime">now() AND now()< "NearlyExpiredTime" THEN '正常'
      WHEN "Status"<300 AND "ExpiredTime"<now() THEN '超期'
ELSE '未知'

END AS "IsOverExpiredTime", "SourceChannel" AS "Source" , CASE

WHEN "TransferPhone" is null or "TransferPhone"='undefined' THEN '12345'
ELSE
    "TransferPhone"

END AS "TransferPhone" , "ActualHandleStepName" AS "ActualHandleStepName" , "ReTransactNum" AS "ReTransactNum" , (select CASE "screentemp"."Status"

WHEN 0 THEN '待办'
WHEN 1 THEN '审批中'
WHEN 2 THEN '审批完成'
WHEN 3 THEN '审批拒绝'
ELSE '-'

END AS "OrderScreenStatus" from order_screen screentemp WHERE "OrderId"="ordertemp"."Id" order by "CreationTime" DESC LIMIT 1) as "OrderScreenStatus", "No" AS "No" , "ProvinceNo" AS "ProvinceNo" , to_char("CreationTime", 'YYYY-MM-DD HH24:MI:SS') AS "StartTime" , "Title" AS "Title" , to_char("ExpiredTime", 'YYYY-MM-DD HH24:MI:SS') AS "ExpiredTime" , "OrgLevelOneName" AS "OrgLevelOneName" ,

CASE

WHEN length("ActualHandleOrgCode")>=9 THEN
    (select "Name" from system_organize orgtemp WHERE orgtemp."Id"="substring"("ordertemp"."ActualHandleOrgCode", 1, 9))
ELSE
    '-'

END AS "OrgLevelTwoName" , "ActualHandleOrgName" AS "ActualHandleOrgName" , to_char("FiledTime", 'YYYY-MM-DD HH24:MI:SS') AS "FiledTime" , "AcceptType" AS "AcceptType" , "HotspotName" AS "HotspotName", "HotspotSpliceName" AS "HotspotSpliceName", CASE

WHEN "Town" is not NULL and "Town" <>'' THEN "Town"

WHEN "County" is not null and "County" <>'' THEN "County"

ELSE "City"

END AS "AreaName", "Address" As "FullAreaName", "AcceptorName" AS "AcceptorName" , "FromName" AS "FromName" , "Contact" AS "Contact" , "FromPhone" AS "FromPhone" , "Street" AS "Address" , '' AS "Obj", CASE "IdentityType"

WHEN 1 THEN '市民'
WHEN 2 THEN '企业'
ELSE
    '市民'

END AS "IdentityType", CASE "FromGender"

WHEN 0 THEN '女士'
WHEN 1 THEN '先生'
ELSE '未知'

END AS "FromGender" , '-' AS "SeatVisitResult", '-' AS "NowEvaluate", "PushType" AS "PushType" , "Content" AS "Content" , "ActualOpinion" AS "ActualOpinion" , "FileOpinion" AS "FileOpinion" , "Id" AS "SugarNav_Id" FROM "order" ordertemp WHERE (( "CreationTime" >= '2024-07-29' ) AND ( "CreationTime" < '2024-08-10' )) AND ( "IsDeleted" = FALSE )ORDER BY "CreationTime" ASC) aaa left join (select DISTINCT CASE visitdetailtemp."SeatEvaluate"

WHEN 0 THEN '默认满意'
WHEN 2 THEN '不满意'
WHEN 4 THEN '满意'
WHEN 5 THEN '非常满意'
WHEN 6 THEN '未接通'
WHEN 7 THEN '未做评价'
ELSE
    '-'

END AS "SeatVisitResult",visittemp."OrderId" as "OrderId" from order_visit visittemp left join order_visit_detail visitdetailtemp on visittemp."Id"= visitdetailtemp."VisitId"
where visittemp."CreationTime">='2024-07-29' and visitdetailtemp."VisitTarget"=10 AND visittemp."VisitState"=30 ) bbb on aaa."SugarNav_Id"=bbb."OrderId" left join (select DISTINCT visitdetailtemptwo."OrgProcessingResults"::JSON->>'Value' as "OrgProcessingResults",visittemptwo."OrderId" from order_visit visittemptwo left join order_visit_detail visitdetailtemptwo on visittemptwo."Id" = visitdetailtemptwo."VisitId" where visittemptwo."CreationTime">='2024-07-29' and visitdetailtemptwo."VisitTarget"=20 AND visittemptwo."VisitState"=30) ccc on aaa."SugarNav_Id"=ccc."OrderId";

超期件查询

select aaa."No",aaa."CreationTime",aaa."Title",aaa."SourceChannel",aaa."ActualHandleOrgName",aaa."ExpiredTime",aaa."FiledTime", CASE

WHEN aaa."StatusInt">=300 THEN
    to_number(date_trunc('day',aaa."FiledTime"::TIMESTAMP-aaa."ExpiredTime" ::TIMESTAMP)::text, '9999')
ELSE
    to_number(date_trunc('day',now()::TIMESTAMP-aaa."ExpiredTime" ::TIMESTAMP)::text, '9999')

END AS OverTimeDayNum, aaa."Status" from ( select "No",to_char("CreationTime", 'YYYY-MM-DD HH24:MI:SS') as "CreationTime","Title","SourceChannel","ActualHandleOrgName",to_char("ExpiredTime", 'YYYY-MM-DD HH24:MI:SS') as "ExpiredTime",to_char("FiledTime", 'YYYY-MM-DD HH24:MI:SS') as "FiledTime", CASE "ordertemp"."Status"

WHEN 500 THEN '已回访'
WHEN 400 THEN '已发布'
WHEN 300 THEN '已归档'
WHEN 200 THEN '会签中'
WHEN 100 THEN '办理中'
WHEN 9 THEN '退回省平台'
WHEN 2 THEN '特提待受理'
WHEN 1 THEN '退回待受理'
WHEN 0 THEN '待受理'
ELSE    '未知'

END AS "Status", CASE WHEN "Status">=300 and "ExpiredTime">"FiledTime" THEN '正常'

      WHEN "Status">=300 AND "ExpiredTime"<"FiledTime" THEN '超期'
        WHEN "Status"<300 AND "ExpiredTime"> now() AND now()> "NearlyExpiredTime" THEN '即将超期'
        WHEN "Status"<300 AND "ExpiredTime">now() AND now()< "NearlyExpiredTime" THEN '正常'
ELSE '未知'

END AS "IsOverExpiredTime", ordertemp."Status" as "StatusInt" from "order" ordertemp where "CreationTime">='2024-06-01' AND "CreationTime"<'2024-07-01') aaa where aaa."IsOverExpiredTime" ='超期' or aaa."IsOverExpiredTime"='未知'

部门延期明细

select CASE "ordertemp"."Status"

WHEN 500 THEN '已回访'
WHEN 400 THEN '已发布'
WHEN 300 THEN '已归档'
WHEN 200 THEN '会签中'
WHEN 100 THEN '办理中'
WHEN 9 THEN '退回省平台'
WHEN 2 THEN '特提待受理'
WHEN 1 THEN '退回待受理'
WHEN 0 THEN '待受理'
ELSE    '未知'

END AS "Status", ordertemp."SourceChannel", ordertemp."ActualHandleStepName", ordertemp."No", to_char(ordertemp."CreationTime", 'YYYY-MM-DD HH24:MI:SS') "CreationTime", ordertemp."Title", to_char(ordertemp."ExpiredTime", 'YYYY-MM-DD HH24:MI:SS') "ExpiredTime", delaytemp."ApplyOrgName", delaytemp."DelayNum", CASE "DelayUnit"

WHEN 2 THEN
    '工作日'
ELSE
    '自然日'

END AS "DelayUnit", to_char(delaytemp."ApplyDelayTime", 'YYYY-MM-DD HH24:MI:SS') "ApplyDelayTime", CASE "DelayState"

WHEN 1 THEN
    to_char(ordertemp."ExpiredTime", 'YYYY-MM-DD HH24:MI:SS')
ELSE
    ''

END AS "AfterDelay", ordertemp."AcceptType", ordertemp."HotspotName", CASE "DelayState"

WHEN 0 THEN
    '审批中'
WHEN 1 THEN
  '同意'
WHEN 2 THEN
  '拒绝'
ELSE
    '未知'

END AS DelayState from order_delay delaytemp left join "order" ordertemp on delaytemp."OrderId"= ordertemp."Id" where ordertemp."CreationTime">='2024-06-01' and ordertemp."CreationTime"<'2024-07-01' and delaytemp."DelayState"<>3

智能回访任务统计

select cc."No" as "No", cc."Title" as "Title", CASE aa."AiOrderVisitState"

WHEN 1 THEN '待执行'
WHEN 2 THEN '暂停中'
WHEN 3 THEN '执行中'
WHEN 4 THEN '失效'
WHEN 5 THEN '呼叫失败'
WHEN 6 THEN '已结束'
ELSE
    '未知'

END AS "AiOrderVisitState", CASE aa."IsSuccess"

WHEN true THEN
    '成功'
ELSE
    '失败'

END AS "IsSuccess", aa."OuterNo" as "OuterNo", cc."FromName" as "FromName", CASE cc."FromGender"

    WHEN 0 THEN '女士'
WHEN 1 THEN '先生'
ELSE '未知'

END AS "FromGender", cc."StartTime" as "StartTime", cc."FiledTime" as "FiledTime", (select CASE qq."SeatEvaluate"

WHEN 0 THEN '默认满意'
WHEN 2 THEN '不满意'
WHEN 4 THEN '满意'
WHEN 5 THEN '非常满意'
WHEN 6 THEN '未接通'
WHEN 7 THEN '未做评价'
ELSE
    '未知'

END AS SeatEvaluate from order_visit_detail qq where qq."VisitId"= aa."OrderVisitId" and qq."VisitTarget"=10 and qq."CreationTime">'2024-06-01' and qq."CreationTime"<'2024-07-24' LIMIT 1) AS SeatEvaluate, ( select ww."OrgProcessingResults"::JSON->>'Value' from order_visit_detail ww WHERE ww."VisitId" = aa."OrderVisitId" AND ww."VisitTarget"=20 and ww."CreationTime">'2024-06-01' and ww."CreationTime"<'2024-07-24' LIMIT 1) AS OrgProcessingResults, (select ee."IsContact" from order_visit_detail ee WHERE ee."VisitId" = aa."OrderVisitId" AND ee."VisitTarget"=20 and ee."CreationTime">'2024-06-01' and ee."CreationTime"<'2024-07-24' LIMIT 1) as "IsContact", (select rr."Volved" from order_visit_detail rr WHERE rr."VisitId" = aa."OrderVisitId" AND rr."VisitTarget"=20 and rr."CreationTime">'2024-06-01' and rr."CreationTime"<'2024-07-24' LIMIT 1) as "IsContact" from ai_order_visit_detail aa left join order_visit bb on aa."OrderVisitId"=bb."Id" left join "order" cc on bb."OrderId"=cc."Id" --left join order_visit_detail dd on bb."Id"=dd."VisitId" where aa."CreationTime" >'2024-07-01' --and dd."VisitTarget"=20

话务量统计

SELECT to_char("CreatedTime",'yyyy-MM-dd') AS "日期" , SUM(( CASE WHEN ( "CallDirection" = 0 ) THEN 1 ELSE 0 END )) AS "呼入总量" , SUM(( CASE WHEN ((( "OnState" = 1 ) AND ( "CallDirection" = 0 )) AND ( "AnsweredTime" IS NOT NULL )) THEN 1 ELSE 0 END )) AS "接通总量" , SUM(( CASE WHEN ((( "Duration" = 0 ) AND ( "RingTimes" <= 5 )) AND ( "RingTimes" > 0 )) THEN 1 ELSE 0 END )) AS "未接通秒挂断" , SUM(( CASE WHEN ((( "CallDirection" = 0 ) AND ( "AnsweredTime" IS NOT NULL )) AND ( "OnState" = 1 )) THEN "Duration" ELSE 0 END )) AS "呼入总时长" , SUM(( CASE WHEN ((( "CallDirection" = 0 ) AND ( "AnsweredTime" IS NOT NULL )) AND ( "Duration" >= 15 )) THEN 1 ELSE 0 END )) AS "有效接通量" , SUM(( CASE WHEN ((( "CallDirection" = 0 ) AND ( "Duration" > 0 )) AND ( "Duration" <= 5 )) THEN 1 ELSE 0 END )) AS "呼入接通秒挂" , SUM(( CASE WHEN (((( "OnState" = 1 ) AND ( "CallDirection" = 0 )) AND ( "AnsweredTime" IS NOT NULL )) AND ( "RingTimes" >= 15 )) THEN 1 ELSE 0 END )) AS "超时接通" , SUM(( CASE WHEN (((( "OnState" = 1 ) AND ( "CallDirection" = 0 )) AND ( "AnsweredTime" IS NOT NULL )) AND ( "Duration" >= 480 )) THEN 1 ELSE 0 END )) AS "超时挂断" , SUM(( CASE WHEN (((( "CallDirection" = 0 ) AND ( "QueueTims" > 0 )) AND ( "RingTimes" = 0 )) AND ( "OnState" = 2 )) THEN 1 ELSE 0 END )) AS "队列挂断" , SUM(( CASE WHEN ((((( "CallDirection" = 0 ) AND ( "BeginIvrTime" IS NOT NULL )) AND NOT( "BeginQueueTime" IS NOT NULL )) AND NOT( "BeginRingTime" IS NOT NULL )) AND ( "OnState" = 2 )) THEN 1 ELSE 0 END )) AS "IVR挂断" , SUM(( CASE WHEN ( "CallDirection" = 1 ) THEN 1 ELSE 0 END )) AS "呼出总量" , SUM(( CASE WHEN ((( "OnState" = 1 ) AND ( "CallDirection" = 1 )) AND ( "AnsweredTime" IS NOT NULL )) THEN 1 ELSE 0 END )) AS "呼出接通量"
FROM "tr_call_record" WHERE (( "CreatedTime" >= '2024-07-01' ) AND ( "CreatedTime" <= '2024-08-01' )) AND (( "Gateway" <> '82826886' ) AND (LENGTH("Gateway") <> 4 ))GROUP BY to_char("CreatedTime",'yyyy-MM-dd') order by "日期"

回访不满意明细

select ordertemp."No", ordertemp."SourceChannel", ordertemp."ReTransactNum", (select CASE "screentemp"."Status"

WHEN 0 THEN '待办'
WHEN 1 THEN '审批中'
WHEN 2 THEN '审批完成'
WHEN 3 THEN '审批拒绝'
ELSE '未甄别'

END AS "OrderScreenStatus" from order_screen screentemp WHERE "OrderId"="ordertemp"."Id" order by "CreationTime" DESC LIMIT 1) as "OrderScreenStatus", ordervisitdetailtemp."VisitContent" as VisitContent, to_char(ordertemp."CreationTime" , 'YYYY-MM-DD HH24:MI:SS')as "StartTime", ordertemp."Title" as "Title", (select "Name" from "user" usertemp WHERE usertemp."Id" = ordervisittemp."EmployeeId" limit 1) as "EmployeeId", ordervisitdetailtemp."VisitOrgName" as "VisitOrgName", to_char(ordertemp."FiledTime", 'YYYY-MM-DD HH24:MI:SS') as "FiledTime", CASE ordervisittemp."VisitType"

WHEN 10 THEN '人工回访'
WHEN 20 THEN '智能语音回访'
WHEN 30 THEN '短信回访'
WHEN 40 THEN '网站回访'
WHEN 50 THEN 'App回访'
WHEN 60 THEN '微信回访'
WHEN 70 THEN '电话回访'
ELSE
    '其他'

END as "VisitType", ordertemp."HotspotSpliceName" as "HotspotSpliceName", to_char(ordervisittemp."VisitTime", 'YYYY-MM-DD HH24:MI:SS') as "VisitTime", ordervisitdetailtemp."OrgProcessingResults"::JSON->>'Value' as "OrgProcessingResults", ordertemp."Content", "ActualOpinion" AS "ActualOpinion" , "FileOpinion" AS "FileOpinion" , "OrgLevelOneName" AS "OrgLevelOneName" , "ActualHandleOrgName" AS "ActualHandleOrgName" from order_visit_detail ordervisitdetailtemp left join order_visit ordervisittemp on ordervisitdetailtemp."VisitId"=ordervisittemp."Id" left join "order" ordertemp on ordervisittemp."OrderId" = ordertemp."Id" WHERE ordervisitdetailtemp."VisitTarget" = 20 and ordervisittemp."VisitTime">='2024-07-01' and ordervisittemp."VisitTime"<'2024-08-01' and (ordervisitdetailtemp."OrgProcessingResults"->>'Key')::INT=2 and ordervisittemp."VisitState"=30

舆情数据

select aaa."GateWay" as 热线号码, aaa."CallInCount" as 呼入, aaa."ConnectCount" as 接通, aaa."NoConnectByeCount" as 未接通秒挂, round(round(aaa."ConnectCount",2)/aaa."CallInCount"*100,2) as 接通率, aaa."EffectiveCount" as 有效接通, aaa."DurationSum"/(aaa."EffectiveCount" + aaa."ConnectByeCount") as 平均时长, aaa."DurationSum" as 通话总时长, aaa."ConnectByeCount" as 接通秒挂, aaa."TimelyAnswerCount" as 及时应答数, round(round(aaa."TimelyAnswerCount",2)/aaa."ConnectCount"*100,2) as 有效率

from (

SELECT
"Gateway" AS "GateWay" , SUM(( CASE WHEN ( "CallDirection" = 0 ) THEN 1 ELSE 0 END )) AS "CallInCount" , SUM(( CASE WHEN (( "CallDirection" = 0 ) AND ( "OnState" = 1 )) THEN 1 ELSE 0 END )) AS "ConnectCount" , SUM(( CASE WHEN (((( "CallDirection" = 0 ) AND ( "Duration" = 0 )) AND ( "RingTimes" <= 5 )) AND ( "RingTimes" > 0 )) THEN 1 ELSE 0 END )) AS "NoConnectByeCount" , SUM(( CASE WHEN (( "CallDirection" = 0 ) AND ( "Duration" >= 15 )) THEN 1 ELSE 0 END )) AS "EffectiveCount" , SUM(( CASE WHEN (( "CallDirection" = 0 ) AND ( "OnState" = 1 )) THEN "Duration" ELSE 0 END )) AS "DurationSum" , SUM(( CASE WHEN ((( "CallDirection" = 0 ) AND ( "Duration" > 0 )) AND ( "Duration" <= 5 )) THEN 1 ELSE 0 END )) AS "ConnectByeCount" , SUM(( CASE WHEN ((( "CallDirection" = 0 ) AND ( "OnState" = 1 )) AND ( "RingTimes" <= 15 )) THEN 1 ELSE 0 END )) AS "TimelyAnswerCount"
FROM "tr_call_record" WHERE (( "CreatedTime" >= '2024-09-12 17:00:00' ) AND ( "CreatedTime" <= '2024-09-13 17:00:00' )) AND ( "Gateway" = '12333' )GROUP BY "Gateway") aaa