本文已参与「新人创作礼」活动，一起开启掘金创作之路。
涉及流程管理相关的SQL语句(一)
- 查询 某 个人审批的流程列表
select g.subcompanyname '申请人公司名称',k.departmentname '申请人部门',h.lastname '申请人',
a.createdate '申请日期',b.receivedate + ' ' + b.receivetime '单据到达日期',
b.operatedate + ' ' + b.operatetime '单据处理日期',
f.typename '流程类型',e.workflowname '流程名称',a.requestname '流程单据名称',
c.lastname '处理人员',l.jobtitlename '岗位',m.nodename '节点名称',
case when b.isremark = 0 then '未操作' when b. isremark= 2 then '已操作' when b. isremark= 4 then '归档'
when b. isremark= 1 then '转发' when b. isremark= 8 then '抄送' else '其他' end '操作类型',
a.requestid '流程请求ID'
from workflow_requestbase a,
workflow_currentoperator b,
HrmResource c,
HrmDepartment d,
workflow_base e,
workflow_type f,
HrmSubCompany g,
HrmResource h,
HrmDepartment k,
workflow_nodebase m,
hrmjobtitles l
where a.requestid = b.requestid
and b.userid = c.id
and e.id = a.workflowid
and f.id = e.workflowtype
and d.id = c.departmentid
and g.id = h.subcompanyid1
and a.creater = h.id
and h.departmentid = k.id
and c.jobtitle = l.id
and m.id=b.nodeid
and c.loginid in('bj')
order by c.lastname,b.receivedate
- 查询节点名称包含部门负责人的所有路径
select distinct b.id, b.workflowname
from WORKFLOW_FLOWNODE a, workflow_base b
where a.nodeid in
(select id from workflow_nodebase t where t.nodename like '%部门负责人%')
and a.workflowid = b.id;
- 流程强制归档
--retract字段值 0：不可收回 1：查看前收回2：查看后收回
--注：执行数据库操作前一定注意备份数据库。
--workflow_function_manage表retract字段\
update workflow_function_manage set retract=1
- 在流程表单中判断某个数是奇数还是偶数
--1、采用字段属性插入sql\
doFieldSQL(" select case when cast( fudianshu2 as int)%2=0 then '偶数' else '奇数' end fudianshu2 from formtable_main_41
where requestid= '$requestid$' ")
--但是只有生成requestid 才能显示值
--2、字段属性插入sql（可实时获取查看）
--sqlsever 赋值字段是浮点数的话用convert函数转下类型
select convert(decimal(15,2),'$7272$')%2
--所得出值0代表偶数 1代表奇数
--oracle用MOD函数取模，请参考以下写法
select mod(nvl('$7272$', 0), 2) from dual
- 流程表单显示部门全路径
--新建单行文本字段 字段属性插入sql：'$1022$'是浏览按钮 部门字段
with Department as(select * from HrmDepartment where id ='$1022$'union all select d.* from
Department, HrmDepartment d where Department.supdepid = d.id)
select departmentname+'/' from Department
- 将某节点的提交时间拉取到表单上，需要表单字段为文本
select top 1 operatedate+' '+operatetime from workflow_requestLog
where requestid = $requestid$ and nodeid = 377 order by operatedate desc,operatetime desc
--其中377为节点id
--如何查询节点id
select * from workflow_nodebase where id in (
select nodeid from workflow_flownode where workflowid = 73)
