流程强制收回记录查询
select * from workflow_requestoperatelog where requestid = 1101955 ;
其中isinvalid=1 表示该操作被强制收回，
invalidid 为执行强制收回操作的用户ID，invaliddate和invalidtime为执行强制收回的日期时间。
E9流程删除
1、删除一条流程delete from workflow_requestbase_dellog where requestid=需要删除的流程id;
delete from workflow_requestbase_dellog where requestid=需要删除的流程id;
delete from workflow_requestLog_dellog where requestid=需要删除的流程id;
delete from workflow_requestdeletelog where request_id=需要删除的流程id;
2、删除多条流程
delete from workflow_requestbase_dellog where requestid in(需要删除的流程id);
delete from workflow_requestbase_dellog where requestid in(需要删除的流程id);
deletefromworkflow_requestLog_dellogwhererequestidin(需要删除的流程id);deletefromworkflow_requestdeletelogwhererequest_idin(需要删除的流程id);
3、删除回收站所有数据（请慎重）
delete from workflow_requestbase_dellog;
delete from workflow_requestbase_dellog;
delete from workflow_requestLog_dellog;
delete from workflow_requestdeletelog;
4、根据requestid彻底删除流程
DELETE FROM workflow_requestlog WHERE requestid=?;
DELETE FROM workflow_nownode WHERE requestid=?;
DELETE FROM workflow_requestoperatelog WHERE requestid=?;
DELETE FROM workflow_requestoperatelog_dtl WHERE requestid=?;
DELETE FROM workflow_currentoperator WHERE requestid=?;
DELETE FROM workflow_requestbase WHERE requestid=?;
E9数据库缓存操作
- /usr/weaver/ecology/WEB-INF/prop/cacheBackList.properties
编辑cacheBackList.properties
2.将视图名添加进去
视图名（或者表名）=视图名（表名）的形式
3.登录OA系统
http://192.168.13.30/commcache/cacheMonitor.jsp
进入系统后将后缀改成/commcache/cacheMonitor.jsp进行访问
4.点击重新加载配置
oracle锁表的查询
1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)
【注】以上两步，可以通过Oracle的管理控制台来执行。
3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）
4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
# kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345
sqlserver锁表查询
SELECT
request_session_id spid,
OBJECT_NAME(
resource_associated_entity_id
) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'
ORDER BY request_session_id ASC
--spid 锁表进程
--tableName 被锁表名
DECLARE
@spid INT
SET @spid = 52--锁表进程
DECLARE
@SQL VARCHAR (1000)
SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)
SELECT
DISTINCT 'DECLARE @spid INT SET @spid = ',request_session_id,' DECLARE @SQL VARCHAR (1000) SET @SQL = ''kill '' + CAST (@spid AS VARCHAR) EXEC (@SQL);' as s
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT' --spid 锁表进程
--tableName 被锁表名
sqlserver递归分部、部门
--由父项递归下级
with cte(id,parentid,text)
as
(--父项
select id,parentid,text from treeview where parentid = 450
union all
--递归结果集中的下级
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.parentid = c.id
)
select id,parentid,text from cte
---------------------
--由子级递归父项
with cte(id,parentid,text)
as
(--下级父项
select id,parentid,text from treeview where id = 450
union all
--递归结果集中的父项
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.id = c.parentid
)
select id,parentid,text from cte
查询建模所有表单和字段
SELECT
e.treeFieldName AS '模块名',
a.tablename,
b.indexdesc,
c.fieldname,
d.labelname,
c.detailtable AS '明细表'
FROM
WORKFLOW_BILL a,
HTMLLABELINDEX b,
workflow_billfield c,
HTMLLABELINFO d,
modetreefield e,
appforminfo f
WHERE
a.id = f.formid
AND a.namelabel = b.id
AND a.id = c.billid
AND c.fieldlabel = d.indexid
AND e.id = f.appid
AND d.languageid = 7
ORDER BY
a.id
查询流程待办信息
-------SQLServer语句：
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') ) --有效流程
------Oracle语句：
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 (nvl(t1.currentstatus,-1) = -1 or (nvl(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') ) --有效流程
查询所有流程表单和字段
SELECT
a.workflowname,
b.fieldname,
c.labelname,
b.detailtable
FROM
workflow_base a,
workflow_billfield b,
HTMLLABELINFO c
WHERE
a.formid = b.billid
AND b.fieldlabel = c.indexid
AND c.languageid = 7
ORDER BY
a.id
流程流转信息查询
select
a.workflowname 流程名称 ,
c.nodename 节点名称 ,
b.linkname 出口 ,
d.nodename 下一节点名称,
b.nodeid 当前节点id ,
b.destnodeid 目标节点id ,
e.rulename,
e.condit
from
workflow_base a,
workflow_nodelink b,
workflow_nodebase c,
workflow_nodebase d,
rule_base e
where
a.id = b.workflowid
and b.nodeid = c.id
and b.destnodeid = d.id
and b.id = e.linkid
order by
a.id asc,
b.id asc
人力字段相关查询
---分部、部门和人员自定义字段存在哪个表？
1、分部自定义字段数据存储表
SELECT * FROM HrmSubcompanyDefined
2、部门自定义字段数据存储表
SELECT * FROM HrmDepartmentDefined
3、人事卡片自定义字段存储表
select * from cus_formfield--自定义字段存储表
select * from cus_fielddata--自定义字段数据存储表
说明：scope='HrmCustomFieldByInfoType' and scopeid=-1(基本信息)
scope='HrmCustomFieldByInfoType' and scopeid=1(个人信息)
scope='HrmCustomFieldByInfoType' and scopeid=3(工作信息)
参加工作日期，入职日期，合同开始日期，试用期结束日期和合同结束日期存储在哪个表，字段名是什么？
----表名--HRMRESOURCE
----对应字段：参加工作日期--WORKSTARTDATE；入职日期--COMPANYSTARTDATE；
合同开始日期--STARTDATE；试用期结束日期--PROBATIONENDDATE；
合同结束日期--ENDDATE
系统矩阵存储在哪个表？
首先检查下 考勤人员所在的考勤组是否开启【允许外勤打卡】(开启后不会影响历史数据)。
查询系统矩阵(分部矩阵 部门矩阵)
select * from MatrixInfo where issystem in (1,2)
select * from MatrixFieldInfo where matrixid in(select id from MatrixInfo where issystem in (1,2))
select * from Matrixtable_91或92
最后 这个 Matrixtable_91或92是 第一条查询查出来 两个系统矩阵id 然后通过 Matrixtable_ 加上id 拼接上的(自定义矩阵也需要拼接)
----人员卡片自定义字段查询
select field0 from cus_fielddata a where a.scope = 'HrmCustomFieldByInfoType' and scopeid=-1 and id='人员id'"
E9清缓存操作
Sql缓存开启后注意事项
- 原则上禁止通过非程序渠道直接修改oa数据库数据。如果一定要修改，请修改完数据后，
chrome浏览器访问/commcache/cacheMonitor.jsp界面，点击重启加载配置。这样操作修改的
数据可以及时生效。 - 如果存在第三方程序修改oa数据库的表，则需要将会修改的表的名称以（名称=名称）的格
式增加到例外配置文件：ecology\WEB-INF\prop\cacheBackList.properties中，然后再使用
重启加载配置，使其生效。 - 如果客户二次开发中存在非RecordSet（系统标准sql操作类）类修改数据库里的表，也需
要将该表名按注意事项2的方式操作，将其加入例外配置文件中。 - 如果客户二次开发中还存在调用自己新建的存储过程，视图，函数（方法）。也需要将存储
过程，视图，函数（方法）中涉及到的表名加入到例外配置文件中
ecology\WEB-INF\prop\cacheBackList.properties。然后再使用重启加载配置，使其生效。 - 集群环境，如果开启sql缓存，必须所有节点全部开启，关闭也必须所有节点同时全部关闭，
否则必然存在缓存不同步问题
sysadmin登录Ecology系统，访问http(s)😕/ecology服务地址/commcache/cacheMonitor.jsp
外部数据源的连接
数据源的自定义调用方式
方式1
如果数据量大有性能问题，不建议使用
RecordSetDataSource rsd = new RecordSetDataSource("数据源名称");
rsd.execute("select a from tablename where 1=1");
if(rsd.next()) {
String a = rsd.getString("a");
}
方式2
该方式获取的数据库连接为java原生的连接实例，按照java操作即可。
执行普通的数据库操作语句
java.sql.Statement statement = conn.createStatement();
ResultSet rSet = statement.executeQuery(sql);
if(rSet.next()) {
rSet.getString("字段名称");
}
执行外部数据源的存储过程
java.sql.Connection conn = null;
String sql = "{call weaver_to_inca_pk.fee_rec_flow(?,?,?,?,?)}";
java.sql.CallableStatement call = null;
//INCA为配置的外部数据源的名称，建议英文，避免出错
weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource)
weaver.general.StaticObj.getServiceByFullname(("datasource.INCA"),
weaver.interfaces.datasource.DataSource.class);
try {
// 得到一个数据库连接
conn = ds.getConnection();
call = conn.prepareCall(sql);
// 对于in参数，赋值 (第几个问号,要赋的值)
call.setInt(1, Util.getIntValue(request.getWorkflowid()));
call.setInt(2, Util.getIntValue(getMainid(request)));
call.setString(3, flow_cmd);
// 对out参数，声明 //(第几个问号，声明的类型)
call.registerOutParameter(4, Types.NUMERIC);
call.registerOutParameter(5, Types.VARCHAR);
// 执行调用
call.execute();
// 取出结果
int exe_fg = call.getInt(4);
String exc_info = Util.null2String(call.getString(5));
ec9.0应用数据库的更新
可变参数的使用（支持大文本字段clob、text等）
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.interfaces.workflow.action.Action;
import weaver.soa.workflow.request.RequestInfo;
public class TestAction extends BaseBean implements Action {
@Override
public String execute(RequestInfo request) {
String mainTale = request.getRequestManager().getBillTableName();
String requestid = request.getRequestid();
RecordSet recordSet = new RecordSet();
//测试发现表名不支持写入动态可变参数中 该写法可避免sql注入
String sqlString = "update "+mainTale+" set fknr= ? ,sf=? where requestid=?";
//动态参数的传参方法如下
Object[] objects = new Object[3];
objects[0] = "这是付款内容多行文本";
objects[1] = "河北1";
objects[2] = requestid;
recordSet.executeUpdate(sqlString, objects);
return SUCCESS;
}
}
Oracle常规操作
切换到oralcle操作
su - oracle
sqlplus
sqlplua /nolog
dba登录
conn /as sysdba
oracle导出
需要在退出sqlplus下执行
expdp cspc/cspc01 directory=expdir dumpfile=oadata201201031.dmp
oracle导入
—删除用户
drop user cspc cascade; — cspc 为用户名
–删除表空间
drop tablespace cspc INCLUDING CONTENTS AND DATAFILES; — cspc为表空间名称
–创建表空间
create tablespace cspc logging datafile '/oa/oadata/cspc.dbf' size 8000M AutoExtend On Next 1024M segment space management auto;
–给表空间挂文件， 表空间下一个单独文件大于32G,单个文件最大支持32G，则需要增加新的文件
alter tablespace cspc add datafile '/oa/oadata/cspcadd1.dbf' size 8000M autoextend on next 1024m ;
—–创建cspc这个用户，密码为test123 对应的表空间为 cspc
CREATE USER cspc（用户名） IDENTIFIED BY test123（密码） DEFAULT TABLESPACE cspc （表空间）TEMPORARY TABLESPACE Temp;
—-用户授权
grant connect,resource to cspc;
grant create view to cspc;
grant create trigger to cspc;
grant DATAPUMP_EXP_FULL_DATABASE ,DATAPUMP_IMP_FULL_DATABASE to cspc;
alter system set "_allow_level_without_connect_by" = true;
–指定导入文件的路径
create directory impdir as '/oafile/back';
---退出sqlplus
exit
执行导入方式1：
—无输出，后台自动导入
nohup impdp cspc/test123 directory=imp_dir dumpfile=oadata210721.dmp logfile=imp210721.log REMAP_SCHEMA=cspc:cspc remap_tablespace=cspc:cspc &
执行导入方式2：
—可以在控制台查看导入的进度执行脚本信息
impdp cspc/test123 directory=imp_dir dumpfile=oadata210721.dmp logfile=imp210721.log REMAP_SCHEMA=cspc:cspc remap_tablespace=cspc:cspc
E9已办查询
已办：支持nvl函数的数据库
t1.requestid in (select t4.requestid from workflow_requestbase t4, workflow_currentoperator t3, workflow_base t2
where t4.requestid = t3.requestid and
t4.workflowid = t2.id and
t3.userid in ('$UserId$') and t3.usertype = 0 and
(nvl(t4.currentstatus,-1) = -1 or (nvl(t4.currentstatus,-1)=0 and
t4.creater in ('$UserId$'))) and
(t4.deleted <> 1 or t4.deleted is null or t4.deleted = '')
and(t3.isremark in ('2', '4')
or (t3.isremark = '0' and t3.takisremark = -2))
and t3.islasttimes = 1
and t2.isvalid in ('1', '3')
union
(select distinct requestid from ofs_done_data where 1=1 and userid in ('$UserId$') and
islasttimes = 1 and isremark in ('2','4') and ofs_done_data.pcurl is not null))
已办：不支持nvl函数的数据库
t1.requestid in (select t4.requestid from workflow_requestbase t4, workflow_currentoperator t3, workflow_base t2
where t4.requestid = t3.requestid and
t4.workflowid = t2.id and
t3.userid in ('$UserId$') and t3.usertype = 0 and
(isNull(t4.currentstatus,-1) = -1 or (isNull(t4.currentstatus,-1)=0 and
t4.creater in ('$UserId$'))) and
(t4.deleted <> 1 or t4.deleted is null or t4.deleted = '')
and(t3.isremark in ('2', '4')
or (t3.isremark = '0' and t3.takisremark = -2))
and t3.islasttimes = 1
and t2.isvalid in ('1', '3')
union
(select distinct requestid from ofs_done_data where 1=1 and userid in ('$UserId$') and
islasttimes = 1 and isremark in ('2','4') and ofs_done_data.pcurl is not null))
已办：mysql
t1.requestid in (select t4.requestid from workflow_requestbase t4, workflow_currentoperator t3, workflow_base t2
where t4.requestid = t3.requestid and
t4.workflowid = t2.id and
t3.userid in ('$UserId$') and t3.usertype = 0 and
(isNull(t4.currentstatus,-1) = -1 or (isNull(t4.currentstatus,-1)=0 and
t4.creater in ('$UserId$'))) and
(t4.deleted <> 1 or t4.deleted is null or t4.deleted = '')
and(t3.isremark in ('2', '4')
or (t3.isremark = '0' and t3.takisremark = -2))
and t3.islasttimes = 1
and t2.isvalid in ('1', '3')
union
(select distinct requestid from ofs_done_data where 1=1 and userid in ('$UserId$') and
islasttimes = 1 and isremark in ('2','4') and ofs_done_data.pcurl is not null))
E9初始化数据库路径
http://192.168.66.137:8088/spa/smallApp/static4engine/engine.html#/system/CreateDB
主子流程关联表
workflow_subwfrequest
矩阵查询
矩阵信息 表：MatrixInfo
矩阵字段信息 表：MatrixFieldInfo 关联字段：matrixid
具体的矩阵信息 表：Matrixtable_ +（MatrixInfo中相关矩阵的ID）
示例如：
select * from MatrixInfo ;
select * from MatrixFieldInfo;
select * from Matrixtable_1;
//系统分部矩阵
UPDATE Matrixtable_1 SET DWFZR = ? WHERE id = subcompanyid
//系统部门矩阵
UPDATE Matrixtable_2 SET bmfzr = ? WHERE id = depatmentid
3736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
