Loading... 对于使用泛微进行二次开发的开发者,如果能够掌握泛微的常用系统表,不管是进行数据初始化还是日常开发查询,都可以达到事半功倍的效果。 **常用系统表** | 表名 | 含义 | 备注 | | --------------------------- | ------------------ | ------------------------ | | hrmresource | 人员表 | | | hrmdepartment | 部门表 | | | hrmsubcompany | 分部表 | | | hrmroles | 角色表 | | | hrmrolemembers | 人员角色表 | | | menucustom | 自定义菜单表 | | | menushareinfo | 自定义菜单权限 | | | workflow_bill | 逻辑表信息表 | 含流程和建模 | | workflow_billfield | 逻辑表字段表 | | | workflow_SelectItem | 自定义下拉框表 | | | HtmlLabelInfo | 中文标签表 | 含表名和字段名等 | | modeinfo | 模块基本信息表 | | | modetreefield | 应用基本信息表 | | | appforminfo | 应用与表单关系表 | | | mode_selectitempage | 公共选择框信息表 | | | mode_selectitempagedetail | 公共选择框明细表 | | | Workflow_type | 流程类型表 | | | Workflow_base | 流程定义表 | | | workflow_requestbase | 流程实例表 | 所有流程实例都走这张表 | | workflow_nodebase | 流程节点表 | | | workflow_currentoperator | 流程当前操作人表 | | **常用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 Last modification:November 3, 2023 © Allow specification reprint Support Appreciate the author AliPayWeChat Like 如果觉得我的文章对你有用,请随意赞赏