### 信件数据查询
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" , 
"CurrentHandleOrgName" AS "CurrentHandleOrgName" , 
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
