分享泛微公司OA系统用于二次开发的sql脚本
本单位用的oa系统就是泛微公司的oa协同办公平台，下面是我对他进行二次开发统计用到的写数据库脚本，只做开发参考使用，对于该系统的二次开发技术交流可以加我q:2050372586
【仪表盘】格式sql编写规则： ? 只支持select语句并且， Select的结果必须为四列一行，且没列值为数值，其中 ? 第一个值为最小值 ? 第二个值为最大值 ? 第三个值为安全值 ? 第四个值为当前值 例如 Select 0,400,300,count(id) from hrmresource 【其他图形】格式sql编写规则（饼状、柱状、折线等等） 只支持select语句并且，select的结果必须为两列N(N>=1)行，且第二列必须为数值，其中 第一列值为显示项目(分类)名称 第二列值为对应项目（分类）的值 例如 员工数机构 sql: select top 10 (select subcompanyname from hrmsubcompany where id=subCompanyId1),count(id) As cids from hrmresource group by subCompanyId1 order by cids desc oracle: select (select subcompanyname from hrmsubcompany where id=subCompanyId1),count(id) As cids from hrmresource group by subCompanyId1 order by cids desc 合同金额前十机构 select top 10 (select subcompanyname from hrmsubcompany where id=subCompanyId1),floor(sum(price/10000)) As cids from CRM_Contract where subCompanyId1 is not null group by subCompanyId1 order by cids desc 销售机会金额前十机构 select top 10 (select subcompanyname from hrmsubcompany where id=subCompanyId),floor(sum(preyield/10000)) As cids from CRM_SellChance group by subCompanyId order by cids desc 耗时前五长流程 sql： select top 5 (select workflowname from workflow_base where id=workflowid),24*avg(convert(float,convert(datetime,lastoperatedate))-convert(float,convert(datetime,createdate))) cids from workflow_requestbase where workflowid>0 and workflowid!=5 group by workflowid order by cids desc oracle： select (select workflowname from workflow_base where id=workflowid),avg(nvl(to_date(lastoperatedate,'yyyy-mm-dd'),to_date(to_char(sysdate,'YYYY-MM-DD'),'yyyy-mm-dd'))- to_date(createdate,'yyyy-mm-dd')) as cids from workflow_requestbase where workflowid>0 and workflowid!=5 and rownum<=5 group by workflowid order by cids desc 销售机会前十机构 select top 10 (select subcompanyname from hrmsubcompany where id=subCompanyId),count(id) As cids from CRM_SellChance group by subCompanyId order by cids desc 项目统计前十机构 select top 10 (select subcompanyname from hrmsubcompany where id=hrmdepartment.subcompanyid1),count(*) as cid from Prj_ProjectInfo ,hrmdepartment where Prj_ProjectInfo.department=hrmdepartment.id group by hrmdepartment.subcompanyid1 order by cid desc 人员性别统计 select case sex when '0' then '男' else '女' end, count(id) as cid from hrmresource group by sex 典型客户前十机构 select top 10 (select provincename from hrmprovince where id=province) ,province,count(id) as cids from CRM_CustomerInfo where status=4 and province>0 group by province order by cids desc 创建文挡前十名统计 sql: select top 10 (select lastname from hrmresource where id=doccreaterid) ,count(id) as cid from docdetail group by doccreaterid order by cid desc oracle: select (select lastname from hrmresource where id=doccreaterid) ,count(id) as cid from docdetail where rownum<=10 group by doccreaterid order by cid desc 待办事宜数量前十机构统计 slq: select top 10 (select subcompanyname from hrmsubcompany where id=subcompanyid1),count(requestid) as cid from workflow_currentoperator,hrmresource where hrmresource.id=workflow_currentoperator.userid and isremark in (0,1,8,9) and islasttimes=1 group by subcompanyid1 order by cid desc oracle: select (select subcompanyname from hrmsubcompany where id=subcompanyid1),count(requestid) as cid from workflow_currentoperator,hrmresource where hrmresource.id=workflow_currentoperator.userid and isremark in (0,1,8,9) and islasttimes=1 and rownum<=10 group by subcompanyid1 order by cid desc 学历人员分布 oracle： select (select name from hrmeducationlevel where id=educationlevel) as 学历,count(id) as 人数 from hrmresource where educationlevel != 0 group by educationlevel order by educationlevel desc 办公地点人员分布 oracle： select (select locationname from hrmlocations where id=locationid) as 办公地点,count(id) as 人数 from hrmresource group by locationid 年龄段人员分布 oracle： select 年龄段,age1 from( select count(id) age1,'40后' 年龄段 from hrmresource t1 where t1.birthday between '1940-01-01' and '1949-12-31' union select count(id) age1,'50后' 年龄段 from hrmresource t1 where t1.birthday between '1950-01-01' and '1959-12-31' union select count(id) age1,'60后' 年龄段 from hrmresource t1 where t1.birthday between '1960-01-01' and '1969-12-31' union select count(id) age1,'70后' 年龄段 from hrmresource t1 where t1.birthday between '1970-01-01' and '1979-12-31' union select count(id) age1,'80后' 年龄段 from hrmresource t1 where t1.birthday between '1980-01-01' and '1989-12-31' union select count(id) age1,'90后' 年龄段 from hrmresource t1 where t1.birthday between '1990-01-01' and '1999-12-31') order by 年龄段 职务人员分布 sql： select count(id) as 人数,'总裁' as 职务 from hrmresource where seclevel=80 union all select count(id) as 人数,'中心领导' as 职务 from hrmresource where seclevel=70 or seclevel=60 union all select count(id) as 人数,'经理' as 职务 from hrmresource where seclevel=40 union all select count(id) as 人数,'副经理' as 职务 from hrmresource where seclevel=30 union all select count(id) as 人数,'主管' as 职务 from hrmresource where seclevel=20 union all select count(id) as 人数,'普通员工' as 职务 from hrmresource where seclevel=10 union all select count(id) as 人数,'实习生' as 职务 from hrmresource where seclevel=5 oracle： select 人数,职务 from( select count(id) as 人数,'总裁' as 职务 from hrmresource where seclevel=80 union all select count(id) as 人数,'中心领导' as 职务 from hrmresource where seclevel=70 or seclevel=60 union all select count(id) as 人数,'经理' as 职务 from hrmresource where seclevel=40 union all select count(id) as 人数,'副经理' as 职务 from hrmresource where seclevel=30 union all select count(id) as 人数,'主管' as 职务 from hrmresource where seclevel=20 union all select count(id) as 人数,'普通员工' as 职务 from hrmresource where seclevel=10 union all select count(id) as 人数,'实习生' as 职务 from hrmresource where level=5 ) U8系统-主营业务收入期间图 select case b.iperiod when '1' then '一月' when '2' then '二月' when '3' then '三月' when '4' then '四月' when '5' then '五月' when '6' then '六月' when '7' then '七月' when '8' then '八月' when '9' then '九月' when '10' then '十月' when '11' then '十一月' when '12' then '十二月' end,sum(b.md) as 金额 from fitemss00 as a,GL_accass as b,code as c where a.citemcode=b.citem_id and b.ccode=c.ccode and c.ccode like '6001%' group by iperiod U8系统-主营业务成本期间图 select case b.iperiod when '1' then '一月' when '2' then '二月' when '3' then '三月' when '4' then '四月' when '5' then '五月' when '6' then '六月' when '7' then '七月' when '8' then '八月' when '9' then '九月' when '10' then '十月' when '11' then '十一月' when '12' then '十二月' end,sum(b.md) as 金额 from fitemss00 as a,GL_accass as b,code as c where a.citemcode=b.citem_id and b.ccode=c.ccode and c.ccode like '6401%' group by iperiod U8系统-主营业务利润 select '成本' as 项,sum(md) as 金额 from fitemss00 as a,GL_accass as b,code as c where a.citemcode=b.citem_id and b.ccode=c.ccode and c.ccode like '6401%' union all select '收入' as 项,sum(mc) as 金额 from fitemss00 as a,GL_accass as b,code as c where a.citemcode=b.citem_id and b.ccode=c.ccode and c.ccode like '6001%' U8系统-主营业务每月利润表 select '一月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='1')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='1') as 金额 union all select '二月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='2')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='2') as 金额 union all select '三月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='3')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='3') as 金额 union all select '四月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='4')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='4') as 金额 union all select '五月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='5')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='5') as 金额 union all select '六月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='6')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='6') as 金额 union all select '七月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='7')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='7') as 金额 union all select '八月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='8')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='8') as 金额 union all select '九月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='9')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='9') as 金额 union all select '十月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='10')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='10') as 金额 union all select '十一月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='11')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='11') as 金额 union all select '十二月' as 月份,(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6001%' and iperiod='12')-(select sum(b.md) from GL_accass as b,code as c where b.ccode=c.ccode and c.ccode like '6401%' and iperiod='12') as 金额 fitemss00 产品 code 科目 fitemss00class 产品分类表 GL_accass 资金表 GL_CashTable 总账现金流量数据表 GL_accvouch 凭证及明细账 Code 会计科目档案 Department 部门基本信息 Person 职员档案 Customer 客户档案 CustomerClass 客户分类档案 Vendor 供应商档案 VendorClass 供应商分类档案 fitemss98 现金流量项目目录 fitemss98class 现金流量分类目录
|
序号 |
查询语句 |
功能 |
|
1 |
Select* from DocDetail where maincategory=17 |
查看通知 |
|
2 |
SELECT * from HrmResource |
查看个人信息 |
|
3 |
select * from workflow_requestbase |
流程申请表 |
|
select * from workflow_requestbase where status='提醒' |
查看会议提醒（） |
|
|
4
|
Select * from workflow_currentoperator where userid=606 and isremark =0 （其中userid是用户信息表的账号） |
查看当前待办流程 |
|
select * from workflow_requestbase WHERE requestid in ( Select requestid from workflow_currentoperator where userid=10 and isremark =0 ) |
|
|
|
5 |
Update workflow_currentoperator |
|
|
6 |
select * from Meeting |
查看会议 |
|
7 |
select * from MeetingRoom |
会议室 |
|
8 |
SELECT a.name,b.lastname,c.lastname,d.name,a.begindate,a.begintime,a.enddate, a.endtime, a.createdate,a.createtime,a.totalmember,a.description,a.requestid FROM Meeting a LEFT JOIN HrmResource b ON a.caller=b.id LEFT JOIN HrmResource c ON a.contacter=c.id LEFT JOIN MeetingRoom d ON a.address=d.id where DateDiff(dd, begindate,getdate())<=14 ORDER BY begindate asc |
14天以后的会议详细查看 |
|
9 |
Where DateDiff(dd,a.createdate,getdate())=0 |
今天的所有数据 |
|
10 |
Where DateDiff(dd,a.createdate,getdate())=1 |
昨天 |
|
11 |
select * from Meeting where DateDiff(dd, begindate,getdate())<=14 ORDER BY begindate desc |
14天内 |
|
12 |
select * from Meeting where DateDiff(mm, createdate,getdate())=0 |
本月申报 |
|
13 |
select * from Meeting where DateDiff(yy, createdate,getdate())=0 |
本年申报 |
|
14 |
HrmDepartment 人力资源部门表 HrmJobGroups 人力资源职务类型表 HrmJobTitles 人力资源岗位表 |
|
|
15 |
select loginid as 工号,lastname as 姓名 from HrmResource where isnull(loginid,'')<>'' |
sql 查询账号不为空的： |
|
16 |
select * from workflow_billfield select * from workflow_base select * from workflow_selectitem
|
一下三个表关联起来可以查询到选择项的对应流程 |
