### 信件数据查询
select aaa.*,bbb."SeatVisitResult" from 
(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",
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",
"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-03-29' ) AND ( "CreationTime" < '2024-07-3' ))  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-03-29' 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