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 and t2.userid in (用户id) and t2.usertype=0  --替换用户的id， 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   (isnull(t1.currentstatus,-1) = -1 or (isnull(t1.currentstatus,-1)=0 and t1.creater in (用户id)))   --替换用户的id，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
and t2.userid = 2881 – 用户ID
and t1.workflowid in(521,76522) – 流程ID
– 下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到已办事宜
and t2.usertype=0 – 用户类型，1为普通用户，2为客户
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
and t2.userid = 1 – 用户ID
and t1.workflowid in(301) – 流程ID
– 下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到待办事宜
and t2.usertype=0 – 用户类型，1为普通用户，2为客户
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
and t2.userid = 120 – 用户ID
and t1.workflowid in(93) – 流程ID
– 下面这些条件没有仔细研究过，我也不知道分别有什么作用，只知道这样可以查到l办结事宜
and t2.usertype=0 – 用户类型，1为普通用户，2为客户
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 <> "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 <> 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 <>"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 <> "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 <>"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 <>"2" or t2.takisremark is null))))
• 抄送 done (t2.preisremark in(8,9))
• 转发 done (t2.preisremark=1 and (t2.takisremark <>"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 <>"2" or t2.takisremark is null))
• 未读 emDoingApp (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")))
• 被督办 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 <> 1))
• 超时已办（非SqlServer和金仓数据库使用） done (t2.overtime > 0 and (t2.isprocessed is null or t2.isprocessed = "3"))
• 超时自动办理（非SqlServer和金仓数据库使用） done (t2.overtime > 0 and (t2.isprocessed in("1","2")))
• 超时已办（SqlServer和金仓数据库使用） done (cast(t2.overtime as bigint) > 0 and (t2.isprocessed is null or t2.isprocessed = "3"))
• 超时自动办理（SqlServer和金仓数据库使用） done (cast(t2.overtime as bigint) > 0 and (t2.isprocessed in("1","2")))

workflow_currentoperator表【isremark】字段说明：

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