### 信件数据查询
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
 


 ###信件清单查询（新）
 select '' as "序号",
aaa.信件状态,aaa."是否超期",aaa.来源,aaa.转接来源,aaa.当前节点,aaa.重办次数,aaa.甄别状态,aaa.受理编号,aaa.省编号,aaa.受理时间,aaa.标题,aaa.期满时间,aaa."一级部门",
aaa.二级部门,aaa."接办部门",aaa."办结时间",aaa."受理类型",aaa."热点类型",aaa.热点全称,aaa."区域",aaa.区域全称,aaa."受理人",aaa."姓名",aaa."联系电话",aaa."来电电话",
aaa."事发地址",aaa."具体对象",aaa."来电人主体",aaa."性别",ccc."SeatVisitResult" as "坐席满意度",bbb."OrgProcessingResults" as "满意度",aaa."推送分类",aaa."受理内容",
aaa."承办意见",aaa."归档意见" 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 "信件状态",
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 "是否超期",
"SourceChannel" AS "来源" , 
CASE 
	WHEN "TransferPhone" is null or "TransferPhone"='undefined' THEN '12345'
	ELSE
		"TransferPhone"
END AS "转接来源" ,
"ActualHandleStepName" AS "当前节点" ,
"ReTransactNum" AS "重办次数" , 
(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 "甄别状态", 
"No" AS "受理编号" , 
"ProvinceNo" AS "省编号" , 
to_char("CreationTime", 'YYYY-MM-DD HH24:MI:SS') AS "受理时间" , 
"Title" AS "标题" , 
to_char("ExpiredTime", 'YYYY-MM-DD HH24:MI:SS') AS "期满时间" , 
"OrgLevelOneName" AS "一级部门" , 

CASE 
	WHEN length("ActualHandleOrgCode")>=9 THEN
		(select "Name" from system_organize orgtemp WHERE orgtemp."Id"="substring"("ordertemp"."ActualHandleOrgCode", 1, 9))
	ELSE
		'-'
END AS "二级部门" , 
"ActualHandleOrgName" AS "接办部门" , 
to_char("FiledTime", 'YYYY-MM-DD HH24:MI:SS') AS "办结时间" , 
"AcceptType" AS "受理类型" , 
"HotspotName" AS "热点类型",
"HotspotSpliceName" AS "热点全称",
CASE 
	WHEN "Town" is not NULL and "Town" <>'' THEN "Town"
  WHEN "County" is not null and "County" <>'' THEN "County"
	ELSE "City"
END AS "区域",
"Address"  As "区域全称",
"AcceptorName" AS "受理人" ,
 "FromName" AS "姓名" , 
 "Contact" AS "联系电话" , 
 "FromPhone" AS "来电电话" , 
 "Street" AS "事发地址" ,
 '' AS "具体对象",
 CASE "IdentityType"
	WHEN 1 THEN '市民'
	WHEN 2 THEN '企业'
	ELSE
		'市民'
END AS "来电人主体",
CASE "FromGender"
	WHEN 0 THEN '女士'
	WHEN 1 THEN '先生'
	ELSE '未知'
END AS "性别" , 
"PushType" AS "推送分类" , 
"Content" AS "受理内容" , 
"ActualOpinion" AS "承办意见" ,
"FileOpinion" AS "归档意见" ,
"Id" AS "SugarNav_Id" FROM "order" ordertemp  WHERE (( "CreationTime" >= '2024-08-29' ) AND ( "CreationTime" <= '2024-08-30' ))  AND ( "IsDeleted" = FALSE )ORDER BY "CreationTime" ASC) aaa
left JOIN
(select 
 string_agg(visitdetailtemptwo."OrgProcessingResults"::JSON->>'Value','/') as "OrgProcessingResults",visittemptwo."OrderId" as "OrderId"
from order_visit visittemptwo
left join order_visit_detail visitdetailtemptwo on visittemptwo."Id" = visitdetailtemptwo."VisitId"
where  visittemptwo."CreationTime">='2024-08-29' and visitdetailtemptwo."VisitTarget"=20 AND visittemptwo."VisitState"=30 group by visittemptwo."OrderId") as bbb
on aaa."SugarNav_Id" = bbb."OrderId"
left join
(select string_agg(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-08-29' and visitdetailtemp."VisitTarget"=10 AND visittemp."VisitState"=30 GROUP BY visittemp."OrderId") as "ccc"
on aaa."SugarNav_Id" = ccc."OrderId"


### 重置工单回访部门信息
update order_visit_detail set 
"VisitOrgCode"= hhh."HandlerOrgId","VisitOrgName"=hhh."HandlerOrgName"
from (
select * from (
select ppp."OrderId",ooo."Id" "VisitDetailId" from(
select "Id","OrderId" from order_visit where "VisitState" <> 50 and "OrderId" in(
select "Id" from "order" WHERE "FiledTime">'2024-10-30' and "ActualHandleOrgCode" <> '001' and "Status">300 and "CounterSignType" is null)) ppp
left join order_visit_detail ooo on ppp."Id"=ooo."VisitId"
WHERE ooo."VisitTarget"=20) qqq
left join 
(select aaa.* from workflow_step aaa
left join (
select "ExternalId","max"("HandleTime") maxTime from workflow_step where "WorkflowId" in(
select "WorkflowId" from "order" WHERE "FiledTime">'2024-10-30' and "ActualHandleOrgCode"<>'001' and "Status">=300 and "CounterSignType" is null)
and  "StepType" not in (2,3) AND "IsOrigin"=TRUE group  by "ExternalId") bbb on aaa."ExternalId"=bbb."ExternalId" 
where aaa."HandleTime"=bbb.maxTime) www on qqq."OrderId"= www."ExternalId") hhh
where order_visit_detail."Id" = hhh."VisitDetailId" and hhh."OrderId"='08dcf7df-964a-436c-8f39-bc96ff54522a'


### 重置工单实际办理部门和接办信息
update  "order" uuu
set
"ActualHandleOrgName"=ccc."HandlerOrgName","ActualHandleOrgCode"=ccc."HandlerOrgId","CurrentHandleOrgId"=ccc."HandlerOrgId","CurrentHandlerName"=ccc."HandlerOrgName"
from (
select aaa."ExternalId",aaa."HandlerOrgId",aaa."HandlerOrgName" from workflow_step aaa
left join (
select "ExternalId","max"("HandleTime") maxTime from workflow_step where "WorkflowId" in(
select "WorkflowId" from "order" WHERE "FiledTime">'2024-10-30' and "ActualHandleOrgCode"<>'001' and "Status">=300 and "CounterSignType" is null)
and  "StepType" not in (2,3) AND "IsOrigin"=TRUE group  by "ExternalId") bbb on aaa."ExternalId"=bbb."ExternalId" 
where aaa."HandleTime"=bbb.maxTime) ccc where uuu."Id"=ccc."ExternalId" and uuu."Id"='08dcf7df-964a-436c-8f39-bc96ff54522a'



### 查询智能回访超过两次的回访工单
update order_visit_detail set "SeatEvaluate"=4,"VisitContent"='市民未接听电话。'
where "VisitId" in(
select "Id" from order_visit WHERE "OrderId" in(
select "OrderId" from ai_order_visit_detail where "CreationTime">'2024-10-29'
and "CallTimes">0 and "AiSeatEvaluate" is null and "AiIsContact" is null and "AiVolved" is  NULL  and "AiOrgProcessingResults" is null
group by "OrderId") and "VisitState" not in(50,30)) and "VisitTarget"=10;


update order_visit_detail set "OrgProcessingResults" ='{"Key":"6","Value":"未接通"}',"VisitContent"='市民未接听电话。',"IsContact"=TRUE,"Volved"=true
where "VisitId" in(
select "Id" from order_visit WHERE "OrderId" in(
select "OrderId" from ai_order_visit_detail where "CreationTime">'2024-10-29'
and "CallTimes">0 and "AiSeatEvaluate" is null and "AiIsContact" is null and "AiVolved" is  NULL  and "AiOrgProcessingResults" is null
group by "OrderId") and "VisitState" not in(50,30)) and "VisitTarget"=20





