【泛微OA】常用SQL语句

辣椒小鱼 • 2020-11-07 • OA/BPM系列, 数据库 • 1580 阅读

将某个子目录下的所有文档共享给某个人查看权限


 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

                

                

                                        

                        THE END
                    

                    

                                            


                    

    
            

            
                    

                

            
    
            分享        

                

            
                                    
                                    
                                    
                                    
                                    
                                    
                                            二维码        

                    

                                打赏            

                    

                        海报        

        

            

            

                
				                    
                
				                    
                
				                    
            

        

            

        

    

    

        
        
    







    

        

        

            
            
【泛微OA】常用SQL语句

            

                
将某个子目录下的所有文档共享给某个人查看权限




 insert into docshare(docid,sharetype,seclevel,rolelevel,sharelevel,userid,foralluser,downloadleve……            

            

                
                

            

        

        

            
        

        

            

        

    





                                            

                                                        

                                

                                    
                                    

                                        
                                            <<上一篇                                        
                                    

                                

                            

                                                        

                                

                                    
                                    

                                        下一篇>>