【泛微】待辦、已辦表數(shù)據(jù)查詢相關

1.流程相關表

流程相關表：

workflow_requestbase
workflow_currentoperator

以下分類數(shù)據(jù)查詢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
取流程節(jié)點信息
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') )  --有效流程
流程節(jié)點時效統(tǒng)計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
已經(jīng)事宜列表
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")
  • 轉發(fā) 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)
  • 轉發(fā)事宜 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))
  • 轉發(fā) done (t2.preisremark=1 and (t2.takisremark <>"2" or t2.takisremark is null))
  • 傳閱 done (t2.preisremark=11)
  • **已辦理(作為節(jié)點操作者處理) done (t2.preisremark=0)
  • 抄送 emDoingApp (t2.isremark in (8,9))
  • 傳閱 emDoingApp (t2.isremark =11)
  • 轉發(fā) 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和金倉數(shù)據(jù)庫使用） done (t2.overtime > 0 and (t2.isprocessed is null or t2.isprocessed = "3"))
  • 超時自動辦理（非SqlServer和金倉數(shù)據(jù)庫使用） done (t2.overtime > 0 and (t2.isprocessed in("1","2")))
  • 超時已辦（SqlServer和金倉數(shù)據(jù)庫使用） done (cast(t2.overtime as bigint) > 0 and (t2.isprocessed is null or t2.isprocessed = "3"))
  • 超時自動辦理（SqlServer和金倉數(shù)據(jù)庫使用） done (cast(t2.overtime as bigint) > 0 and (t2.isprocessed in("1","2")))
workflow_currentoperator表【isremark】字段說明：
  • 節(jié)點操作者（takisremark等于-2時在已辦顯示，表示流程意見征詢出去別人還未回復，takisremark等于其他值時在待辦顯示）
  • 當takisremark等于2時是意見征詢接收人，takisremark等于其他值時是轉發(fā)接收人
  • 已處理流程
  • 已處理流程（歸檔節(jié)點操作者數(shù)據(jù)）
  • 超時指定干預人數(shù)據(jù)
  • 自動處理過程中的數(shù)據(jù)
  • 協(xié)辦人數(shù)據(jù)
  • 抄送不需提交
  • 抄送需提交
  • 傳閱接收人
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。
禁止轉載，如需轉載請通過簡信或評論聯(lián)系作者。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容