对于使用泛微进行二次开发的开发者，如果能够掌握泛微的常用系统表，不管是进行数据初始化还是日常开发查询，都可以达到事半功倍的效果。

常用系统表

常用SQL脚本

有了上面的表信息，我们就可以根据上面的表信息构造一些常用的sql脚本。

注：本篇sql脚本使用的oracle数据库。

1.给自定义菜单赋权

例如我们初始化自定义菜单权限的时候，需要给很多菜单赋予相同的角色权限，可以先通过界面操作给一个菜单赋予角色权限，然后利用以下语句给其他菜单赋权。

获取菜单ID：

例如“我的工作”的为初始化的菜单id为：121

select * from MENUCUSTOM where menuname like '%我的工作%' and menutype=1624261345XX4

“我的待办”id为122

select * from MENUCUSTOM where menuname like '%我的待办%' and menutype=1624261345XX4

把“我的工作”角色权限赋予“我的待办”：

insert into MENUSHAREINFO(resourceid,resourcetype,infoid,menutype,sharetype,sharevalue,seclevel,rolelevel,customid,jobtitlelevel,jobtitlesharevalue)

select resourceid,resourcetype,122 infoid,menutype,sharetype,sharevalue,seclevel,rolelevel,customid,jobtitlelevel,jobtitlesharevalue

from MENUSHAREINFO where customid=1624261345234 and infoid=121

2.获取表的自定义下拉框选项

例如获取表uf_xxx_dict, xt字段的下拉框选项

SELECT selectname

FROM

workflow_SelectItem

WHERE

fieldid IN ( SELECT id FROM workflow_billfield WHERE billid =

( SELECT id FROM workflow_bill WHERE tablename = 'uf_xxx_dict' ) AND fieldname = 'xt' );

3.获取分部信息

获取某个公司（ID：21）下的分部信息

select * from hrmsubcompany where canceled=0 and supsubcomid=21

4.获取表的字段信息

例如获取逻辑表uf_xxx_projectinfo的字段名、中文标识、字段类型

SELECT

f.fieldname,

o.labelname,

F.FIELDDBTYPE

FROM

workflow_billfield f,

workflow_bill b,

HtmlLabelInfo o

WHERE f.billid = b.id

and f.fieldlabel = o.indexid

and o.languageid = 7

AND b.tablename = 'uf_xxx_projectinfo'

5.查询子表

例如：查询uf_xxx_projectinfo表的子表

SELECT

distinct b.tablename,o.LABELNAME,

f.detailtable

FROM

workflow_billfield f,

workflow_bill b,

HtmlLabelInfo o

WHERE f.billid = b.id

and b.namelabel = o.indexid

and o.languageid = 7 and b.tablename like '%uf_xxx_projectinfo%' order by b.tablename,f.detailtable

获取指定模块下的表单和子表信息：

例如获取（21,522,523,1021,1022）模块下的表单表名和子表信息

SELECT distinct b.tablename,o.LABELNAME,f.detailtable

FROM

workflow_billfield f,

workflow_bill b,

HtmlLabelInfo o

WHERE f.billid = b.id

and b.namelabel = o.indexid and b.id in(

select formid from MODEINFO where modetype in(21,522,523,1021,1022))

and o.languageid = 7 order by b.tablename,f.detailtable

根据流程类型获取表信息：

根据流程类型获取流程表单和子表信息

SELECT distinct b.tablename,o.LABELNAME, f.detailtable

FROM

workflow_billfield f,

workflow_bill b,

HtmlLabelInfo o

WHERE f.billid = b.id

and b.namelabel = o.indexid and b.id in(

select formid from workflow_base where workflowtype in(

select id from WORKFLOW_TYPE where typename in('高层XX'

,'预估XX','交付XX','XX管理','XX系统')

)) and o.languageid = 7 order by b.tablename,f.detailtable

6.查询字段名称包含“项目”的表和子表

SELECT

distinct b.tablename,(select labelname from HtmlLabelInfo where indexid=b.namelabel and languageid = 7) tablename,f.detailtable

FROM

workflow_billfield f,

workflow_bill b,

HtmlLabelInfo o

WHERE f.billid = b.id

and f.fieldlabel = o.indexid

and o.languageid = 7 and o.labelname like '%项目%' order by b.tablename,f.detailtable

7.根据应用获取表单ID

select a.formid from appforminfo a,MODETREEFIELD b where a.appid=b.id

and B.TREEFIELDNAME in('营XX管理系统','交XX管理系统','采XX系统','XX厂商/品牌','XX分析报告','XX印章系统')

8.获取公共选择框选项及值

例如获取名称为“XX状态”的公共选择框选项名称和值

select a.name,a.disorder from mode_selectitempagedetail a,mode_selectitempage b where a.mainid=b.id and B.SELECTITEMNAME='XX状态'

9.查询角色人员分部

例如查询角色为('项目经理','项目总监')的人员信息和分部信息

select c.rolesmark as "角色",b.loginid as "登录名",b.lastname as "姓名",(select subcompanyname from hrmsubcompany where id=b.subcompanyid1 ) as "分部"

from hrmrolemembers a,hrmresource b,hrmroles c

where a.roleid=c.id and a.resourceid=b.id and c.rolesmark in('项目经理','项目总监')

10.按流程类型统计流程数

按流程类型从高到底统计流程实例数

select b.typename,count(*) cc from workflow_requestbase a,(select m.id,n.typename from workflow_base m,workflow_type n where m.workflowtype=n.id) b where a.workflowid=b.id

group by b.typename

order by cc desc

11.按流程名称统计流程数

select b.workflowname,count(*) cc from workflow_requestbase a, workflow_base b where a.workflowid=b.id

group by b.workflowname

order by cc desc

12.流程时效

以下语句可以查询整个流程的时效和流程节点的时效，可以将以下语句写成视图随时查询

select a.workflowname "流程名称",b.typename "流程类型",D.REQUESTID "流程ID",D.REQUESTNAMENEW "流程标题",

(select nodename from workflow_nodebase where id=D.CURRENTNODEID) "当前节点",

decode(D.CURRENTNODETYPE,0,'创建',1,'批准',2,'提交',3,'归档') CURRENTNODETYPE,

D.CREATER "创建人ID",

(select lastname from hrmresource where id=D.CREATER) "创建人",D.CREATEDATE "创建日期",D.CREATETIME "创建时间",D.LASTOPERATOR "最后操作人ID",

(select lastname from hrmresource where id=D.LASTOPERATOR) "最后操作人",D.LASTOPERATEDATE "最后操作日期",D.LASTOPERATETIME "最后操作时间",

case when d.LASTOPERATEDATE is not null then

round((to_date(D.LASTOPERATEDATE||' '||D.LASTOPERATETIME,'yyyy-MM-dd HH24:mi:ss')-to_date(D.CREATEDATE||' '||D.CREATETIME,'yyyy-MM-dd HH24:mi:ss'))*24*60,2)

else null end "流程时效(分钟)",

E.NODENAME "节点名称",

C.USERID "节点操作人ID",f.lastname "节点操作人",

c.receivedate "节点到达日期",c.receivetime "节点到达时间",c.operatedate "节点操作日期",c.operatetime "节点操作时间",

case when c.operatedate is not null then

round((to_date(c.operatedate||' '||c.operatetime,'yyyy-MM-dd HH24:mi:ss')-to_date(c.receivedate||' '||c.receivetime,'yyyy-MM-dd HH24:mi:ss'))*24*60,2)

else null end "节点时效(分钟)"

from workflow_base a,workflow_type b,workflow_currentoperator c,workflow_requestbase d,workflow_nodebase e ,hrmresource f

where A.WORKFLOWTYPE=b.id and A.ID=D.WORKFLOWID and C.REQUESTID=D.REQUESTID and C.NODEID=E.ID and C.USERID=f.id;

13.根据流程表单查询流程信息

例如查询流程表单（formtable_main_65、formtable_main_61）相关的流程信息

SELECT k.xmid,

t.requestid,

T.LASTOPERATEDATE,

T.LASTOPERATETIME,

k.mc

|| '('

|| T.LASTOPERATEDATE

|| ' '

|| T.LASTOPERATETIME

|| ')'

lcmc,

T.LASTOPERATOR,

t.requestnamenew,

t.creater,

t.createdate,

t.createtime

FROM workflow_requestbase t

INNER JOIN (SELECT requestid,

xmxx xmid,

'XXX交付申请' mc

FROM formtable_main_65

UNION

SELECT requestid,

xmxx xmid,

'XXX交付变更' mc

FROM formtable_main_61

) k

ON k.requestid = t.requestid

WHERE t.currentnodetype = 3

以上为我们在二次开发中常用的sql脚本，如果需要详细的表结构信息，可以咨询泛微客户或联系我们it