### 信件数据查询
select aaa.*,bbb."SeatVisitResult" 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",
"FirstVisitResult" AS "FirstVisitResult" , 
"PushType" AS "PushType" , 
"Content" AS "Content" , 
"ActualOpinion" AS "ActualOpinion" ,
"FileOpinion" AS "FileOpinion" ,
"Id" AS "SugarNav_Id" FROM "order" ordertemp  WHERE (( "CreationTime" >= '2024-07-17' ) AND ( "CreationTime" < '2024-07-24' ))  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-17' and visitdetailtemp."VisitTarget"=10 AND visittemp."VisitState"=30 ) bbb on aaa."SugarNav_Id"=bbb."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
