【泛微】待办、已办表数据查询相关

泛微二开 – 待办、已办表数据查询相关

---

前言

---

1.流程相关表

workflow_requestbase
workflow_currentoperator

以下分类数据查询sql：

select 
a.typeid,b.indexdesc,a.scope 
from workflow_dimension a,htmllabelindex b 
where a.typetitle = b.id order by typeid

取全部流程模型

select * from workflow_base where ISVALID = 1

取流程节点信息

select * from workflow_nodebase n 
join workflow_flownode f on f.nodeid = n.id 
where WORKFLOWID = 93

oa查询某人的所有待办流程

SELECT
	t1.requestid,
	t1.requestname,
	t2.* 
FROM
	workflow_requestbase t1,
	workflow_currentoperator t2 
WHERE
	( t1.deleted <> 1 OR t1.deleted IS NULL OR t1.deleted = '' ) 
AND t1.requestid = t2.requestid 
-- 替换用户的 id
AND t2.userid IN ('1') 
AND t2.usertype = 0 
AND ((
		t2.isremark = 0 
		AND ( t2.takisremark IS NULL OR t2.takisremark = 0 )) 
OR t2.isremark IN ( '1', '5', '8', '9', '7' )) 
AND ( t1.deleted = 0 OR t1.deleted IS NULL ) 
AND t2.islasttimes = 1 
AND (
	IFNULL(t1.currentstatus,-1) = -1 
	OR (
		IFNULL(t1.currentstatus,-1)= 0 
	-- 替换用户的 id， 
	AND t1.creater IN ('1')))
-- 有效流程
AND t1.workflowid IN ( SELECT id FROM workflow_base WHERE ( isvalid = '1' OR isvalid = '3' ) ) 

流程节点时效统计SQL

SELECT
	rb.requestid,
	rb.`REQUESTNAME`,
	r2.LASTNAME,
	c.isremark,
	cast( CONCAT( RECEIVEDATE, ' ', RECEIVETIME ) AS datetime ) '接收时间',
	cast( CONCAT( OPERATEDATE, ' ', OPERATETIME ) AS datetime ) '处理时间',
	cast( CONCAT( firstviewdate, ' ', firstviewtime ) AS datetime ) '首次查看',
	timediff(
		cast( CONCAT( firstviewdate, ' ', firstviewtime ) AS datetime ),
	cast( CONCAT( RECEIVEDATE, ' ', RECEIVETIME ) AS datetime )) '查看间隔',
	timediff(
		cast( CONCAT( OPERATEDATE, ' ', OPERATETIME ) AS datetime ),
	cast( CONCAT( RECEIVEDATE, ' ', RECEIVETIME ) AS datetime )) '处理耗时' 
FROM
	workflow_currentoperator C
	JOIN workflow_requestbase rb ON c.requestid = rb.requestid
	LEFT JOIN hrmresource r2 ON c.userid = r2.ID 
WHERE
	c.WORKFLOWID = 93 
	AND nodeid = 699 
	AND firstviewdate IS NOT NULL 
ORDER BY
	c.RECEIVEDATE DESC

已经事宜列表

select distinct t1.requestid, t1.createdate, t1.createtime,t1.creater, t1.creatertype, t1.workflowid,
t1.requestname, t1.status,t1.requestlevel,t1.currentnodeid,t2.viewtype,t2.receivedate,
t2.receivetime,t2.operatedate,t2.operatetime,t2.isremark,t2.nodeid,t2.agentorbyagentid,t2.agenttype
from workflow_requestbase t1,workflow_currentoperator t2
where t1.requestid = t2.requestid
--  用户ID
and t2.userid = 2881 
--  流程ID
and t1.workflowid in(521,76522) 
--  下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到已办事宜
-- 用户类型，1为普通用户，2为客户
and t2.usertype=0 
and t2.isremark in(2,4)
and t2.iscomplete=1
and t2.islasttimes=1
-- 按时间倒序
order by t2.operatedate Desc ,t2.operatetime Desc

待办事宜列表

select distinct t1.requestid, t1.createdate, t1.createtime,t1.creater, t1.creatertype,
t1.workflowid, t1.requestname, t1.status,t1.requestlevel,t1.currentnodeid,t2.viewtype,
t2.receivedate,t2.receivetime,t2.isremark,t2.nodeid,t2.agentorbyagentid,t2.agenttype
from workflow_requestbase t1,workflow_currentoperator t2
where t1.requestid = t2.requestid
 --  用户ID
and t2.userid = 1
--  流程ID
and t1.workflowid in(301) 
--  下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到待办事宜
--  用户类型，1为普通用户，2为客户
and t2.usertype=0 
and t2.isremark in( '0','1','5','8','9','7')
and t2.islasttimes=1
-- 按时间倒序
order by t2.receivedate ,t2.receivetime Desc

办结事宜列表

select distinct t1.requestid, t1.createdate, t1.createtime,t1.creater, t1.creatertype, t1.workflowid,
t1.requestname, t1.status,t1.requestlevel,t1.currentnodeid,t2.viewtype,t2.receivedate,t2.receivetime,
t2.isremark,t2.nodeid,t2.agentorbyagentid,t2.agenttype
from workflow_requestbase t1,workflow_currentoperator t2
where t1.requestid = t2.requestid
-- 用户ID
and t2.userid = 120 
--  流程ID
and t1.workflowid in(93) 
-- 下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到l办结事宜
-- 用户类型，1为普通用户，2为客户
and t2.usertype=0 
and t2.isremark in('2','4')
and t1.currentnodetype = '3'
and iscomplete=1
and islasttimes=1
-- 按时间倒序
order by t2.receivedate ,t2.receivetime Desc

2.流程sql查询维度文档

workflow_dimension(流程待办维度):

全部    doing   
((t2.isremark=0 and (t2.takisremark is null or t2.takisremark="0" )) or t2.isremark in(1,5,7,8,9,11))

未读    doing   
(t2.isremark not in(5) and t2.isprocessed is null and t2.viewtype = "0" and (t1.currentnodetype <> "3" or (t2.isremark in (1,8,9,11) and t1.currentnodetype = "3")))

反馈    doing   暂不提供

超时    doing   
((t2.isremark = "5" or (t2.isremark = "0" and (t2.takisremark is null or t2.takisremark=0 ) and t2.isprocessed is not null)) and (t2.iscomplete <> 1))

被督办   doing   
(t2.requestid in (select requestid from workflow_requestlog where logtype="s"))

待处理   doing   
(t2.isremark not in(1,8,9,11) or (t2.isremark=1 and t2.takisremark="2"))

待阅    doing   
((t2.isremark in(8,9,11) or (t2.isremark=1 and (t2.takisremark <>"2" or t2.takisremark is null))))

被退回   doing   
(t2.isbereject="1")

转发    doing   
(t2.isremark =1 and (t2.takisremark <>"2" or t2.takisremark is null))

抄送    doing   
(t2.isremark in (8,9))

全部    done    
(t2.isremark in(2,4) or (t2.isremark=0 and t2.takisremark ="-2"))

未归档   done    
((t2.isremark =2 or (t2.isremark=0 and t2.takisremark = "-2")) and t2.iscomplete=0)

已归档   done    
(t2.iscomplete=1 and t1.currentnodetype = "3")

待回复   done    
(t2.isremark=0 and t2.takisremark = "-2")

未读    done    
(t2.viewtype=0)

反馈    done    
暂不提供

全部    mine    
(t1.creater in (#userid#) and t1.creatertype = 0 and t1.creater = t2.userid and ((t1.currentnodetype <> "3") or (t2.isremark in(1,2,4,5,8,9,11) and t1.currentnodetype="3")))----【#userid#是占位符，后台拼接这种条件时会自动将#userid#替换成当前登录用户的userid】

未归档   mine    
(t1.currentnodetype <> "3")

已归档   mine    
((t2.isremark in(1,2,4,5,8,9,11) or (t2.isremark=0 and t2.takisremark =-2)) and t1.currentnodetype = "3")

未读    mine    
(t2.viewtype=0)

反馈    mine    暂不提供

待办事宜  portal  
((t2.isremark=0 and (t2.takisremark is null or t2.takisremark="0" )) or t2.isremark in(1,5,7,8,9,11))

待阅事宜  portal  
(t2.isremark in(8,9,11) or (t2.isremark=1 and (t2.takisremark <>"2" or t2.takisremark is null)))

退回事宜  portal  
(((t2.isremark=0 and (t2.takisremark is null or t2.takisremark="0" )) or t2.isremark in(1,5,7,8,9,11)) and (t2.isbereject="1"))

已办事宜  portal  
(t2.isremark in(2,4) or (t2.isremark=0 and t2.takisremark ="-2"))

办结事宜  portal  
(t2.iscomplete=1 and t1.currentnodetype = "3")

我的请求  portal  
(t1.creater in (#userid#) and t1.creatertype = 0 and t1.creater = t2.userid and ((t1.currentnodetype &lt;&gt; "3") or (t2.isremark in(1,2,4,5,8,9,11) and t1.currentnodetype="3")))----【#userid#是占位符，后台拼接这种条件时会自动将#userid#替换成当前登录用户的userid】

抄送事宜  portal  
(t2.isremark in (8,9))

督办事宜  portal  red这里的督办列表和待办的被督办tab不同，sql无法给出

超时事宜  portal  
((t2.isremark = "5" or (t2.isremark = "0" and (t2.takisremark is null or t2.takisremark=0 ) and t2.isprocessed is not null)) and (t2.iscomplete &lt;&gt; 1))

反馈事宜  portal  暂不提供

传阅跟踪  portal  
exists(select 1 from WORKFLOW_CHUANYUE a,workflow_currentoperator b where a.requestid = b.requestid and a.requestid=t2.requestid and a.resourceid=t2.userid and a.userid = b.userid and b.isremark = 11 and a.issubmitsign=1)

所有事宜  portal  不需要条件

我的关注  doing   
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

我的关注  done    
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

我的关注  mine    
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

我的关注  portal  
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

全部    emDoingApp  
((t2.isremark=0 and (t2.takisremark is null or t2.takisremark="0" )) or t2.isremark in(1,5,7,8,9,11))

待处理   emDoingApp  
(t2.isremark not in(1,8,9,11) or (t2.isremark=1 and t2.takisremark="2"))

待阅    emDoingApp  
((t2.isremark in(8,9,11) or (t2.isremark=1 and (t2.takisremark &lt;&gt;"2" or t2.takisremark is null))))

被退回   emDoingApp  
(t2.isbereject="1")

全部    emDoneApp   
(t2.isremark in(2,4) or (t2.isremark=0 and t2.takisremark ="-2"))

未归档   emDoneApp   
((t2.isremark =2 or (t2.isremark=0 and t2.takisremark = "-2")) and t2.iscomplete=0)

已归档   emDoneApp   
(t2.iscomplete=1 and t1.currentnodetype = "3")

待回复   emDoneApp   
(t2.isremark=0 and t2.takisremark = "-2")

全部    emMineApp   
(t1.creater in (#userid#) and t1.creatertype = 0 and t1.creater = t2.userid and ((t1.currentnodetype &lt;&gt; "3") or (t2.isremark in(1,2,4,5,8,9,11) and t1.currentnodetype="3")))----【#userid#是占位符，后台拼接这种条件时会自动将#userid#替换成当前登录用户的userid】

办结    emFinApp    
(t2.iscomplete=1 and t1.currentnodetype = "3")

抄送    emCopyApp   
(t2.isremark in (8,9))

我的关注  emDoingApp  
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

我的关注  emDoneApp   
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

我的关注  emMineApp   
exists (SELECT 1 FROM workflow_attention att WHERE att.requestid=t2.requestid and att.userid=t2.userid AND att.usertype=0)

转发事宜  portal  
(t2.isremark=1 and (t2.takisremark &lt;&gt;"2" or t2.takisremark is null))

传阅    doing   
(t2.isremark=11)

已处理(对应待处理)    done    
(t2.preisremark not in(1,8,9,11) or (t2.preisremark=1 and t2.takisremark="2"))

已阅(对应待阅)  done    
((t2.preisremark in(8,9,11) or (t2.preisremark=1 and (t2.takisremark &lt;&gt;"2" or t2.takisremark is null))))

抄送    done    
(t2.preisremark in(8,9))

转发    done    
(t2.preisremark=1 and (t2.takisremark &lt;&gt;"2" or t2.takisremark is null))

传阅    done    
(t2.preisremark=11)

**已办理(作为节点操作者处理)  done    
(t2.preisremark=0)

抄送    emDoingApp  
(t2.isremark in (8,9))

传阅    emDoingApp  
(t2.isremark =11)

转发    emDoingApp  
(t2.isremark =1 and (t2.takisremark &lt;&gt;"2" or t2.takisremark is null))

未读    emDoingApp  
(t2.isremark not in(5) and t2.isprocessed is null and t2.viewtype = "0" and (t1.currentnodetype &lt;&gt; "3" or (t2.isremark in (1,8,9,11) and t1.currentnodetype = "3")))

被督办   emDoingApp  
(t2.requestid in (select requestid from workflow_requestlog where logtype="s"))

超时    emDoingApp  
((t2.isremark = "5" or (t2.isremark = "0" and (t2.takisremark is null or t2.takisremark=0 ) and t2.isprocessed is not null)) and (t2.iscomplete &lt;&gt; 1))

超时已办（非SqlServer和金仓数据库使用）  done    
(t2.overtime &gt; 0 and (t2.isprocessed is null or t2.isprocessed = "3"))

超时自动办理（非SqlServer和金仓数据库使用）    done    
(t2.overtime &gt; 0 and (t2.isprocessed in("1","2")))

超时已办（SqlServer和金仓数据库使用）   done    
(cast(t2.overtime as bigint) &gt; 0 and (t2.isprocessed is null or t2.isprocessed = "3"))

超时自动办理（SqlServer和金仓数据库使用） done    
(cast(t2.overtime as bigint) &gt; 0 and (t2.isprocessed in("1","2")))

workflow_currentoperator表【isremark】字段说明：

节点操作者（takisremark等于-2时在已办显示，表示流程意见征询出去别人还未回复，takisremark等于其他值时在待办显示）
当takisremark等于2时是意见征询接收人，takisremark等于其他值时是转发接收人
已处理流程
已处理流程（归档节点操作者数据）
超时指定干预人数据
自动处理过程中的数据
协办人数据
抄送不需提交
抄送需提交
传阅接收人

总结