【泛微OA】常用SQL语句
将某个子目录下的所有文档共享给某个人查看权限
insert into docshare(docid,sharetype,seclevel,rolelevel,sharelevel,userid,foralluser,downloadlevel)
select id,1,0,0,1,用户id,0,1 from docdetail where seccategory=子目录id
考勤表 移动轨迹业务签到表
--考勤签到表：
select * from hrmschedulesign;
--移动轨迹签到表：(包含 考勤签到表hrmschedulesign的数据)
select * from mobile_sign;
----------------------------------分--------------割--------------线-----------------------------------
8.0中将第一个节点批量设置为可强制归档。
update workflow_function_manage ss
set pigeonhole = '1'
where exists (select *
from (select workflowid, min(operatortype) nodeid
from workflow_function_manage
where operatortype > 0
group by workflowid) t
where t.workflowid = ss.workflowid
and t.nodeid = ss.operatortype)
----------------------------------分--------------割--------------线-----------------------------------
人员头像、人员照片路径存储数据表字段：
1.人员的照片存放在服务器的路径 查询如下：
--imagefile表字段filerealpath存储文件路径
select a.id,
a.loginid,
a.lastname,
a.status,
a.resourceimageid,
b.imagefileid,
b.imagefilename,
b.imagefiletype,
b.filerealpath
from hrmresource a, imagefile b
where a.resourceimageid = b.imagefileid
and id=24
and (a.resourceimageid != 0 or a.resourceimageid != '');
--2、人员头像相关：
--HrmResource表字段messagerurl存储文件路径
select HrmResource. from HrmResource where id='人员id';
--imagefile表字段filerealpath存储文件路径
select * from imagefile where filerealpath ='文件路径';
select messagerurl from HrmResource;
----------------------------------分--------------割--------------线-----------------------------------
如何删除工作微博
select * from blog_discuss where userid= 发表人id order by id desc
这个查询这个人发表的所有内容
delete from blog_discuss where id=微博id
找到内容后，用id删除
----------------------------------分--------------割--------------线-----------------------------------
如果要在【请假类型】为"其它带薪假"下的【其他请假类型】
增加4个类型：{换休、节育手术假、看护假、生理假}
SQL语句如下：
(1)先查询：通过原【其他请假类型】中'哺乳假'来确定其fieldid值为655
select * from workflow_SelectItem where selectname='哺乳假';
select * from workflow_SelectItem where fieldid=655;
(2)执行如下新增语句：
insert into workflow_SelectItem(fieldid,isbill,selectvalue,selectname,listorder,isdefault,isAccordToSubCom) values(655,1,12,'换休',12.00,'n',0)
go
insert into workflow_SelectItem(fieldid,isbill,selectvalue,selectname,listorder,isdefault,isAccordToSubCom) values(655,1,13,'节育手术假',13.00,'n',0)
go
insert into workflow_SelectItem(fieldid,isbill,selectvalue,selectname,listorder,isdefault,isAccordToSubCom) values(655,1,14,'看护假',14.00,'n',0)
go
insert into workflow_SelectItem(fieldid,isbill,selectvalue,selectname,listorder,isdefault,isAccordToSubCom) values(655,1,15,'生理假',15.00,'n',0)
go
(3)屏蔽掉不需要的假别：
update workflow_SelectItem set workflow_SelectItem.Cancel='1' where fieldid=655 and id=89
go
----------------------------------分--------------割--------------线-----------------------------------
查询OA流程中节点出现超时内容的记录数
select A.*,B.workflowname,c.lastname from workflow_requestLog A left outer join workflow_base B
on A.workflowid=B.id
left outer join hrmresource C
on A.operator=c.id
where A.remark like '%该审批节点已超时，系统默认同意并跳过%'
order by workflowid
查询OA流程中节点出现超时内容的记录数（增加了流程标题名）
select A.*,B.workflowname as '超时流程名',c.lastname as '超时操作人名',D.requestname as '超时流程标题'
from workflow_requestLog A left outer join workflow_base B
on A.workflowid=B.id
left outer join hrmresource C
on A.operator=c.id
left outer join workflow_requestbase D
on A.requestid=D.requestid
where A.remark like '%该审批节点已超时，系统默认同意并跳过%'
order by workflowid
----------------------------------分--------------割--------------线-----------------------------------
E7部门、分部自定义自字段表相关：
--分部表
select * from hrmsubcompany;
--分部自定义表:fieldname字段值对应就是分部表中对应的一列
select * from subcompanydefinefield;
--和流程关联表：关联分部自定义字段 subcompanyfield
select * from workflow_groupdetail;
--部门表
select * from hrmdepartment;
--部门自定义表:fieldname字段值对应就是部门表中对应的一列
select * from departmentdefinefield;
--和流程关联表：关联部门自定义字段 deptfild
select * from workflow_groupdetail;
------E8分部、部门 自定义字段表：
--1、分部基础表
SELECT * FROM HrmSubCompany
--2、分部自定义字段数据存储表
SELECT * FROM HrmSubcompanyDefined
--3、部门基础表
SELECT * FROM HrmDepartment
--4、部门自定义字段数据存储表
SELECT * FROM HrmDepartmentDefined
--5、分部、部门自定义字段类型信息存储表：
--通过分组表hrm_fieldgroup中grouptype值区别是分部、部门自定义字段：
--个人基础信息固定字段（grouptype:-1）、个人信息固定字段（grouptype:1）、工作信息固定字段（grouptype:3）、分部（grouptype:4）、部门（grouptype:5）所有字段定义
SELECT * FROM hrm_formfield;
--6、分组表
SELECT * FROM hrm_fieldgroup;
----------------------------------分--------------割--------------线-----------------------------------
修改授权页面
\ecology\systeminfo\version.jsp
前端版本修改页面（关键字showVersion）
\ecology\wui\theme\ecology8\page\toolbar.jsp
后端版本修改页面（关键字showVersion）
\ecology\wui\theme\ecology8\page\toolbarForMiddle.jsp
修改授权名称
select * from
HtmlLabelInfo where indexid=23714
update HtmlLabelInfo set labelname = 'XXXXXXXXXXX' where indexid = '23714' and languageid = '7'
update HtmlLabelInfo set labelname = 'XXXXXXXXXXX' where indexid = '23714' and languageid = '8'
update HtmlLabelInfo set labelname = 'XXXXXXXXXXX' where indexid = '23714' and languageid = '9'
----------------------------------分--------------割--------------线-----------------------------------
修改人事档案状态
修改某一个人update HrmResource set status=1 where id= ？？、
数据库表为：HrmResource执行以下语句
update HrmResource set status=1
0：试用
1：正式
2：临时
3：试用延期
4：解聘
5：离职
6：退休
7：无效
----------------------------------分--------------割--------------线-----------------------------------
修改所有人的密码为1
update hrmresource set password='C4CA4238A0B923820DCC509A6F75849B'
更改所有管理员的密码为1
update hrmresourcemanager set password='C4CA4238A0B923820DCC509A6F75849B'
----------------------------------分--------------割--------------线-----------------------------------
查询下某个目录的文档共享情况
select a.docsubject,a.doccreatedate, a.doccreatetime
,case when b.type=1 then '个人' when b.type=2 then '分部' when b.type=3 then '部门' when b.type=5 then '所有人' else '其他' end 权限类型
,b.content,h.lastname,s.subcompanyname,d.departmentname
,case when b.sharelevel=1 then '查看' when b.sharelevel=2 then '编辑' when b.sharelevel=3 then '全部' else '其他' end 共享情况
from DocDetail a join shareinnerdoc b on a.id=b.sourceid and a.seccategory=目录id
left join HrmResource h on b.content=h.id and b.type=1
left join HrmSubCompany s on b.content=s.id and b.type=2
left join HrmDepartment d on b.content=d.id and b.type=3
order by a.doclastmoddate ,a.doclastmodtime desc
----------------------------------分--------------割--------------线-----------------------------------
统计某人在某段时间内在系统各协作区的发言情况——
select items.id 协作编号,items.name 协作名称
,hrm.lastname 发言人,discuss.createdate 发表日期,discuss.createtime 发表时间
,discuss.floornum 楼号,discuss.remark 发言内容
from cowork_discuss discuss
join cowork_items items on discuss.coworkid = items.id
join hrmresource hrm on discuss.discussant = hrm.id
where hrm.loginid='人员登录号' and discuss.createdate between '开始日期' and '结束日期'
order by items.createdate,items.name,discuss.createdate,discuss.createtime;
----------------------------------分--------------割--------------线-----------------------------------
批量隐藏前台左侧菜单sql
update leftmenuconfig set visible = 0 where infoid = '菜单ID'
批量隐藏后台菜单sql
update mainmenuconfig set visible = 0 where infoid = '菜单ID'
----------------------------------分--------------割--------------线-----------------------------------
删除已发送的内部邮件
select* from rom mailresource where subject like '%邮件标题%'
delete from mailresource where subject like '%邮件标题%'
----------------------------------分--------------割--------------线-----------------------------------
--查询流程对应数据库表名：
select b.tablename from workflow_bill b ,workflow_base s,workflow_requestbase t where t.requestid='35389' and t.workflowid = s.id and b.id=s.formid
--查询某条流程对应数据库表单内容：
select * from formtable_main_60 where requestid='35389'
--修改某条流程对应数据库表单内容：
update formtable_main_60 set sqrq='2015-08-04' ,lcbh='308201508041961' where requestid='35387'
--查询该流程对应的签字意见栏位：
select * from workflow_requestlog where requestid='35389'and operatedate='2015-11-03'
--修改该流程对应的签字意见栏位：
update workflow_requestlog set operatedate='2015-08-04' where requestid='35389'and operatedate='2015-11-03'
--查询流程的流程状态
Select * from workflow_currentoperator where requestid='35389'and operatedate='2015-11-03'
--修改流程的流程状态
update workflow_currentoperator set receivedate='2015-08-04' where requestid='35389'and receivedate='2015-11-03'
--查询流程的流程标题
Select * from workflow_requestbase where requestid='35387'
--修改流程的流程标题、流程状态栏目、查询流程页面中的创建时间、更新时间
update workflow_requestbase set createdate='2015-08-04',lastoperatedate='2015-08-04',requestmark='308201508041961',
requestname='JZHT-3.08付款审批流程（非销售）-总部-方正-2015-08-04'where requestid='35388'
--查询流程的流程状态
Select * from workflow_currentoperator where requestid='35389'and operatedate='2015-11-04'
Select * from workflow_currentoperator where requestid='35389'and receivedate = '2015-11-03'
--修改流程的流程状态
update workflow_currentoperator set operatedate='2015-08-04' where requestid='35389'and operatedate='2015-11-04'
update workflow_currentoperator set receivedate='2015-08-04' where requestid='35389'and receivedate='2015-11-04'
----------------------------------分--------------割--------------线-----------------------------------
查询已经安装了哪些升级包：
select * from ecologyuplist order by label
----------------------------------分--------------割--------------线-----------------------------------
已经使用的浮点数，需要修改小数位（只适用于SQL server，3为其中小数位）：
update workflow_billfield set fielddbtype = 'decimal(15,3)' where id = 字段ID
alter table formtable_main_* alter column float1 decimal(15,3) null *为表单ID
或者新建一个字段，然后将历时数据保存下来，再删除字段；
update formtable_main_表单ID set 新字段=旧字段;
----------------------------------分--------------割--------------线-----------------------------------
如何将历史的文档共享给某个部门。
insert into shareinnerdoc (sourceid,type,content,seclevel,sharelevel,srcfrom,opuser,sharesource,downloadlevel)
select id,3,部门id,安全级别,1,3,部门id,0,1 from docdetail where seccategory = 子目录id
insert into docshare (docid,sharetype,seclevel,rolelevel,sharelevel,departmentid,roleid,foralluser,orggroupid,downloadlevel)
select id,3,安全级别,2,1,部门id,0,0,0,1 from docdetail where seccategory = 子目录id
----------------------------------分--------------割--------------线-----------------------------------
将所有目录的是否允许默认共享、非默认共享，勾选为“是”：
update DocSecCategory set allownModiMShareL=1,shareable=1
将所有目录的禁止文档下载不勾选：
update DocSecCategory set nodownload=0
----------------------------------分--------------割--------------线-----------------------------------
怎样查询某个用户（如ID=174)她曾经退回过的有哪些流程？
SELECT t.typename AS '类型', b.workflowname AS '路径名称', r.requestname AS '流程请求名称', r.requestid
FROM workflow_currentoperator c, workflow_requestbase r, workflow_base b, workflow_type t
WHERE c.requestid = r.requestid AND c.workflowid = b.id AND c.workflowtype = t.id
AND c.ISreject = 1 and c.userid = 174
----------------------------------分--------------割--------------线-----------------------------------
协助区要删除某个人的某条回复记录
参考脚本：
select * from cowork_items where id=主题id
select * from cowork_discuss where coworkid=主题id
delete cowork_discuss where coworkid=主题id and id=你要删的回复id
----------------------------------分--------------割--------------线-----------------------------------
select * from MailResourceFile
查询我的通信 邮件的附件（邮件的附件地址）
select * from MailResource
查询我的通信 邮件信息（包括邮件主题，接收人，发送人，内容等）
----------------------------------分--------------割--------------线-----------------------------------
已进行的网上调查，需要再加一个投票人。
insert into votingshare(votingid,sharetype,resourceid)values(votingid,1,resourceid)
insert into votingsharedetail(votingid,resourceid)values(votingid,resourceid)
----------------------------------分--------------割--------------线-----------------------------------
需要每年1月1号把年假大于5天的全部调整为5天，其它不变，如何批量操作？
update HrmAnnualManagement set annualdays = 5 where annualdays > 5
----------------------------------分--------------割--------------线-----------------------------------
查询A的所有待办流程：
select distinct(a.requestid),b.requestname from workflow_currentoperator a,workflow_requestbase where isremark in （0,1,8,9） and a.requestid = b.requestid and useid = A的id
----------------------------------分--------------割--------------线-----------------------------------
资产删除步骤
select * from CptCapitalAssortment
select * from cptcapital
select * from CptUseLog
select * from CptCapitalShareInfo
select * from CptShareDetail
--首先查询需要删除的资产 isdata=2表示资产
select * from cptcapital where isdata=2
--确认资产删除的资产ID后开始删除以下几个表数据
delete from CptUseLog where capitalid in(select id from cptcapital where isdata=2)
delete from CptCapitalShareInfo where relateditemid in(select id from cptcapital where isdata=2)
delete from CptShareDetail where cptid in (select id from cptcapital where isdata=2)
delete from cptcapital where isdata=2
--如果清空所有数据包括资产资料请直接删除表数据不需要where条件，清空上述表后还需对资产组数据做下更改
update CptCapitalAssortment set capitalcount=0
----------------------------------分--------------割--------------线-----------------------------------
把所有客户共享给某个人编辑权限
insert into crm_shareinfo (relateditemid,sharetype,sharelevel,userid,contents)
select id,1,2,人员id,人员id from crm_customerinfo
----------------------------------分--------------割--------------线-----------------------------------
查询某个时间段某个人处理过的流程id，流程名称以及创建人：
select requestid,(select requestname from workflow_requestbase where requestid = a.requestid),(select lastname from hrmresource where id =(select creater from workflow_requestbase where requestid = a.requestid)) from workflow_currentoperator a where a.operatedate = '2012-11-12' and a.operatetime >='14:00:00' and a.operatetime <='15:00:00' and userid = 1
----------------------------------分--------------割--------------线-----------------------------------
查询oa系统流程的使用情况：
select c.id,c.workflowname,(case d.num when 1 then 0 else d.num end) from workflow_base c, (select a.id, count() as num from workflow_base a left join workflow_requestbase b on a.id = b.workflowid group by a.id ) d where c.id = d.id order by d.num desc,c.id
----------------------------------分--------------割--------------线-----------------------------------
删除员工的工作信息
delete from HrmStatusHistory where resourceid=“人员ID”
----------------------------------分--------------割--------------线-----------------------------------
在数据库中将已经办结但出现在代办事宜中的流程从待办中去除的sql：
update workflow_currentoperator set isremark='2' where isremark='0' and requestid=**
and exists(select 1 from workflow_requestbase a where workflow_currentoperator.requestid=a.requestid and a.currentnodetype='3')
----------------------------------分--------------割--------------线-----------------------------------
流程不能修改提示被签出解决办法：
update workflow_base set isedit=0 where id=流程ID
----------------------------------分--------------割--------------线-----------------------------------
查询某段期间内未登陆系统的人员：
select * from HrmResource where not exists
(select distinct relatedid from sysmaintenancelog where relatedid = HrmResource.id and operatetype = 6 and operatedate between '2007-01-01' and '2007-12-31' ) and status in (0,1,2,3)
----------------------------------分--------------割--------------线-----------------------------------
据文档存放路径查询文档的方法如下：
select a.id,a.docsubject,a.doccreatedate,a.doccreatetime from docdetail a,docimagefile b
where a.id=b.docid and exists(select 1 from imagefile c where c.imagefileid=b.imagefileid and c.filerealpath='文档存放路径')
----------------------------------分--------------割--------------线-----------------------------------
每人每天最后一次登陆系统的时间
select relatedid,(select lastname from hrmresource where id = relatedid),operatedate,max(operatetime) from sysmaintenancelog where operatetype = 6 group by relatedid,operatedate order by relatedid ,operatedate
在某个期间内每人每天最后一次登陆系统的时间
select relatedid,(select lastname from hrmresource where id = relatedid),operatedate,max(operatetime) from sysmaintenancelog where operatetype = 6 and operatedate between '2012-01-01' and '2012-12-31' group by relatedid,operatedate order by relatedid ,operatedate
----------------------------------分--------------割--------------线-----------------------------------
如客户不小心误删掉客户，可以通过以下方法恢复。
select * from CRM_CustomerInfo where name = ''
update CRM_CustomerInfo set deleted =1 where name =''
----------------------------------分--------------割--------------线-----------------------------------
将所有分部下的某个左侧菜单隐藏
update leftmenuconfig set visible = 0 where infoid = 菜单id and resourcetype = 2
----------------------------------分--------------割--------------线-----------------------------------
查询流程的表单信息
先获取到流程的workflowid（可通过新建流程时候通过地址栏获取到id）
select * from workflow_base where id=[workflowid]，得到formid和isbill的值
isbill为1的情况
select * from workflow_bill where id=[formid]
tablename就是该表单数据存放的数据库表
isbill为0的情况
select * from workflow_form where requestid=[流程id]
----------------------------------分--------------割--------------线-----------------------------------
查询不满3个月离职的员工
Oracle:
select * from (select a.id,a.loginid,a.lastname,add_months(to_date(a.createdate,'rrrr_mm_dd'),对比月份) as stardate,to_date(b.hiredate,'rrrr-mm-dd') as enddate from hrmresource a,(
select max(h1.changedate) as hiredate ,resourceid from HrmStatusHistory h1 where h1.type_n = 5 group by resourceid ) b where a.id = b.resourceid and a.status = 5) where stardate > enddate
SQLServer:
select * from (select a.id,a.loginid,a.lastname,a.createdate as a,CONVERT(varchar(12),DATEADD(MONTH,对比月份,a.createdate),23) as createdate,b.hiredate
from hrmresource a,(select max(h1.changedate) as hiredate ,resourceid from HrmStatusHistory h1 where h1.type_n = 5 group by resourceid ) b
where a.id = b.resourceid and a.status = 5) c where c.createdate > c.hiredate
把 对比月份 换成 3 即可查询不满三个月就离职的员工，改成12就可以查询不满一年离职的员工，依次类推
----------------------------------分--------------割--------------线-----------------------------------
查询流程代理的详细情况，包括代理人和被代理人账号，姓名，代理流程，开始结束时间：
select c.loginid as 代理人账号, c.lastname as 代理人姓名,d.loginid as 被代理人账号,d.lastname as 被代理人姓名,c.workflowname as 代理流程,c.begindate as 开始日期,
c.begintime as 开始时间,c.enddate as 结束日期,c.endtime as 结束时间 from
(select h.loginid ,h.lastname,w.begindate,w.begintime,w.enddate,w.endtime ,w.agentid,b.workflowname from workflow_agent w,hrmresource h, workflow_base b
where w.agenterid = h.id and w.workflowid = b.id) c,
(select r.loginid ,r.lastname,a.agentid from workflow_agent a,hrmresource r where a.beagenterid = r.id) d
where c.agentid = d.agentid
