泛微OA使用到的SQL语句

工号(姓名带出):
doFieldSQL(" select workcode,workcode from HrmResource where id = $6319$")
入职时间(姓名带出):
doFieldSQL(" select b.field1,b.field1
from HrmResource a
left join (select id,field1,field30,field31 from cus_fielddata where scopeid = -1) b on a.id = b.id
where a.id = $6319$ ")
学历(姓名带出):
doFieldSQL(" select e.name,a.educationlevel from HrmResource a left join HrmEducationLevel e on a.educationlevel = e.id where a.id = $6319$")
部门(姓名带出):
doFieldSQL(" select departmentname,departmentid from HrmResource hrm,HrmDepartment dept where hrm.departmentid = dept.id and hrm.id =$6319$")
岗位(姓名带出):
doFieldSQL(" select title.jobtitlename,hrm.jobtitle from HrmResource hrm,HrmJobTitles title where hrm.jobtitle = title.id and hrm.id = $6319$ ")
直属上级(姓名带出):
doFieldSQL(" select hrm2.lastname,hrm2.id from HrmResource hrm1,HrmResource hrm2 where hrm1.managerid = hrm2.id and hrm1.id = $6319$")
直属上级工号:
doFieldSQL(" select a.workcode,a.workcode from HrmResource a inner join HrmDepartment b on a.id = b.coadjutant where b.id = $6327$ ")
身份类别(姓名带出):
doFieldSQL("select
(select jobgroupname from HrmJobGroups a1 where id = (select c1.jobgroupid from HrmJobActivities c1 where c1.id = (select b1.jobactivityid from HrmJobTitles b1 where a.jobtitle = b1.id))),
(select jobgroupname from HrmJobGroups a1 where id = (select c1.jobgroupid from HrmJobActivities c1 where c1.id = (select b1.jobactivityid from HrmJobTitles b1 where a.jobtitle = b1.id)))
From HrmResource a where a.id = $6319$ ")
职级(姓名带出):
doFieldSQL(" select (select top 1
case zj when 0 then ‘高管‘ 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 ‘一线组长‘ end
from uf_gwjbxx_dt1 b where a.jobtitle = b.gw and isnull(b.zj,‘-1‘) <> ‘-1‘ order by mainid desc),(select top 1
case zj when 0 then ‘高管‘ 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 ‘一线组长‘ end
from uf_gwjbxx_dt1 b where a.jobtitle = b.gw and isnull(b.zj,‘-1‘) <> ‘-1‘ order by mainid desc) From HrmResource a
where a.id = $6319$ ")
主任+经理(姓名带出)
doFieldSQL("select zr+‘,‘+jl from Matrixtable_7 where bm=$37866$ and ssgs=$37869$")
岗位津贴:
doFieldSQL("select top 1 gwjt,gwjt
from uf_gwjbxx_dt1 b where b.gw = $6328$ and isnull(gwjt,0) <> 0 order by mainid desc ")

入职时间:
doFieldSQL(" select b.field1,b.field1
from HrmResource a
left join (select id,field1,field30,field31 from cus_fielddata where scopeid = -1) b on a.id = b.id
where a.id = $6290$ ")
性别:
doFieldSQL(" select case a.sex when 0 then ‘男‘ when 1 then ‘女‘ end,case a.sex when 0 then ‘男‘ when 1 then ‘女‘ end from HrmResource a where a.id = $6394$ ")
年龄:
doFieldSQL(" select dbo.FUN_2ZT_CalculateAge(a.birthday),dbo.FUN_2ZT_CalculateAge(a.birthday)
from HrmResource a where a.id = $6394$")
安全级别:
doFieldSQL(" select a.seclevel,a.seclevel
from HrmResource a where a.id = $6394$ ")
合同开始:
doFieldSQL(" select top 1 mx_htks,mx_htks from uf_htxx_dt1 a1 inner join uf_htxx a2 on a1.mainid = a2.id where isnull(a2.requestId,‘‘) = ‘‘ and a1.mx_xm = $6394$ order by a1.mx_htks desc ")
合同结束:
doFieldSQL("select top 1 mx_htjs,mx_htjs from uf_htxx_dt1 a1 inner join uf_htxx a2 on a1.mainid = a2.id where isnull(a2.requestId,‘‘) = ‘‘ and a1.mx_xm = $6394$ order by mx_htks desc ")
合同首签日:
doFieldSQL(" select top 1 mx_htsqr,mx_htsqr from uf_htxx_dt1 a1 inner join uf_htxx a2 on a1.mainid = a2.id where isnull(a2.requestId,‘‘) = ‘‘ and a1.mx_xm = $6394$ order by mx_htks desc ")
电话
doFieldSQL(" select telephone,telephone from HrmResource where id = $姓名字段的fieldid$ ")
分机
doFieldSQL(" select telephone,telephone from HrmResource where status <> 5 and id = $11720$ ")
电子邮箱:
doFieldSQL(" select email,email from HrmResource where id = $姓名字段的fieldid$ ")

部门协办人(条件:部门):
doFieldSQL("select a.lastname,a.id from HrmResource a
inner join HrmDepartment b on a.id = b.coadjutant where b.id =$6327$ ")

所属公司/分部(条件:部门):
doFieldSQL(" select a.subcompanyname,a.id from HrmSubCompany a inner join HrmDepartment b on a.id=b.subcompanyid1 where b.id=$6322$ ")

用工类型:
doFieldSQL(" select
case a.field30 when 0 then ‘正式‘ when 1 then ‘试用‘ when 2 then ‘实习‘ when 3 then ‘临时‘ when 4 then ‘食堂‘ when 5 then ‘残障‘ when 6 then ‘退休返聘‘ end,
case a.field30 when 0 then ‘正式‘ when 1 then ‘试用‘ when 2 then ‘实习‘ when 3 then ‘临时‘ when 4 then ‘食堂‘ when 5 then ‘残障‘ when 6 then ‘退休返聘‘ end
from cus_fielddata a
where a.scopeid = -1 and a.id = $6290$ ")

收文单位主管(条件:收文单位,多部门)
doFieldSQL("select dbo.Func_2XL_SplitHrmDepartmentCoadjutantLastName(‘$15314$‘,‘,‘),dbo.Func_2XL_SplitHrmDepartmentCoadjutant(‘$15314$‘,‘,‘) from systemset ")

明细表选择下拉框跳出备注(条件:下拉框)
doFieldSQL("select case $15738$ when 0 then ‘例:yq00001‘ when 1 then ‘例:yq00001‘ when 2 then ‘例:yq00001‘ when 3 then ‘yq00001,密码:intretech@123
密码要求数字和字母特殊字符8位数以上‘ when 4 then ‘例:00-30-40-50-60-70‘ when 5 then ‘例:账号:jack 密码:1049‘ end, case $15738$ when 0 then ‘例:yq00001‘ when 1 then ‘例:yq00001‘ when 2 then ‘例:yq00001‘ when 3 then ‘yq00001,密码:intretech@123密码要求数字和字母特殊字符8位数以上‘ when 4 then ‘例:00-30-40-50-60-70‘ when 5 then ‘例:账号:jack 密码:1049‘ end from systemset")

工号(多人力资源)
doFieldSQL(" select dbo.Func_2XL_SplitHrmResourceWorkcode(‘$11752$‘,‘,‘),dbo.Func_2XL_SplitHrmResourceWorkcode(‘$11752$‘,‘,‘)")

直接上级(多人力资源)
doFieldSQL(" select dbo.Func_2XL_SplitHrmResourceManagerLastname(‘$11752$‘,‘,‘),dbo.Func_2XL_SplitHrmResourceManager(‘$11752$‘,‘,‘)from systemset")

部门(多人力资源)
doFieldSQL(" select dbo.Func_2XL_SplitHrmResourceDeptName(‘$11752$‘,‘,‘),dbo.Func_2XL_SplitHrmResourceDeptName(‘$11752$‘,‘,‘)")

专业:
doFieldSQL(" select field12,field12 from cus_fielddata where scopeid = 1 and id =$6897$ ")


姓名(工号)
doFieldSQL(" select lastname,id from HrmResource where workcode = ‘$18497$‘")

部门(工号)
doFieldSQL(" select departmentname,departmentid from HrmResource hrm,HrmDepartment dept where hrm.departmentid = dept.id and hrm.workcode =‘$18497$‘")

文档链接
/docs/docs/docdsp.jsp?id=3916

查询盈点总人数
select count (lastname) from HrmResource a1 left join HrmSubCompany a on a.id = a1.subcompanyid1 where accounttype = 0 and a1.status <> 5 and a.id =18


doFieldSQL("select case $10941$ when 14 then ‘曾令发‘ when 17 then ‘曾令发‘ when 18 then ‘曾令发‘ when 20 then ‘曾令发‘ when 6 then ‘陈财亮‘ when 7 then ‘吴小龙‘ end,case $10941$ when 14 then ‘88‘ when 17 then ‘88‘ when 18 then ‘88‘ when 20 then ‘88‘ when 6 then ‘4115‘ when 7 then ‘2367‘ end from systemset ")
doFieldSQL("select case $10941$ when 14 then ‘黄英杰‘ when 17 then ‘黄英杰‘ when 18 then ‘黄英杰‘ when 20 then ‘黄英杰‘ when 6 then ‘郑小平‘ when 7 then ‘左振勋‘ end,case $10941$ when 14 then ‘44‘ when 17 then ‘44‘ when 18 then ‘44‘ when 20 then ‘44‘ when 6 then ‘4143‘ when 7 then ‘2324‘ end from systemset ")
doFieldSQL("select case $10941$ when 14 then ‘林仁炬‘ when 17 then ‘林仁炬‘ when 18 then ‘林仁炬‘ when 20 then ‘林仁炬‘ when 6 then ‘许模堂‘ when 7 then ‘肖丽卿‘ end,case $10941$ when 14 then ‘294‘ when 17 then ‘44‘ when 18 then ‘294‘ when 20 then ‘294‘ when 6 then ‘4110‘ when 7 then ‘2030‘ end from systemset ")


中心/部门(工号)
doFieldSQL(" select dbo.FUN_2XL_GetDepartment(a.departmentid,2),
(select id from HrmDepartment a1 where a1.departmentname=dbo.FUN_2XL_GetDepartment(a.departmentid,2) and a1.subcompanyid1=a.subcompanyid1 and tlevel=2)
from HrmResource a
where a.loginid=‘$20176$‘ ")

服务到期日
doFieldSQL(" select dbo.Func_2LZ_WBPXYDFWQRQ($约定服务期限$,‘$服务开始日期$‘),dbo.Func_2LZ_WBPXYDFWQRQ($约定服务期限$,‘$服务开始日期$‘) from systemset")

已服务期限
doFieldSQL(" SELECT CASE ‘$服务开始日期$‘ WHEN ‘‘ THEN 0 ELSE DATEDIFF(mm, ‘$服务开始日期$‘,GETDATE() )END ,CASE ‘$服务开始日期$‘ WHEN ‘‘ THEN 0 ELSE DATEDIFF(mm, ‘$服务开始日期$‘,GETDATE() )END ")

人事状态(姓名)
doFieldSQL(" select selectname,field31
from cus_fielddata a inner join workflow_SelectItem b on a.field31=b.selectvalue
where scopeid=-1 and b.fieldid=6154 and a.id=$20177$ ")

当前操作者
doFieldSQL(" select lastname,id from hrmresource where id= $currentuser$ ")

奖金额度(评分)--全员改善提案
doFieldSQL(" select CASE
WHEN $19585$ >=1 AND $19585$<=10 THEN ‘0‘
WHEN $19585$ >=11 AND $19585$ <=20 THEN ‘10‘
WHEN $19585$ >=21 AND $19585$ <=30 THEN ‘30‘
WHEN $19585$ >=31 AND $19585$ <=40 THEN ‘50‘
WHEN $19585$ >=41 AND $19585$ <=50 THEN ‘80‘
WHEN $19585$ >=51 AND $19585$ <=60 THEN ‘120‘
WHEN $19585$ >=61 AND $19585$ <=75 THEN ‘200‘
WHEN $19585$ >=76 AND $19585$ <=100 THEN ‘400‘
WHEN $19585$ >=101 AND $19585$ <=110 THEN ‘800‘
WHEN $19585$ >=111 AND $19585$ <=120 THEN ‘1000‘
WHEN $19585$ >=121 THEN ‘2000‘
ELSE 0 END ,CASE
WHEN $19585$ >=1 AND $19585$ <=10 THEN ‘0‘
WHEN $19585$ >=11 AND $19585$ <=20 THEN ‘10‘
WHEN $19585$ >=21 AND $19585$ <=30 THEN ‘30‘
WHEN $19585$ >=31 AND $19585$ <=40 THEN ‘50‘
WHEN $19585$ >=41 AND $19585$ <=50 THEN ‘80‘
WHEN $19585$ >=51 AND $19585$ <=60 THEN ‘120‘
WHEN $19585$ >=61 AND $19585$ <=75 THEN ‘200‘
WHEN $19585$ >=76 AND $19585$ <=100 THEN ‘400‘
WHEN $19585$ >=101 AND $19585$ <=110 THEN ‘800‘
WHEN $19585$ >=111 AND $19585$ <=120 THEN ‘1000‘
WHEN $19585$ >=121 THEN ‘2000‘
ELSE 0 END ")


部门协办人
doFieldSQL("select a.lastname,a.id from HrmResource a
inner join HrmDepartment b on a.id = b.coadjutant where b.id =$6327$ ")

当前操作者
doFieldSQL(" select lastname,id from hrmresource where id=$currentuser$ ")

带出部门主管和文员
doFieldSQL("select dbo.Func_2XL_SplitHrmDepartmentCoadjutant(‘19,20‘,‘,‘)+‘,‘+dbo.Func_2YT_SplitHrmDepartmentClerk(‘19,20‘,‘,‘) ")


状态
doFieldSQL("select selectname
from cus_fielddata a inner join cus_selectitem b on a.field31=b.selectvalue
where scopeid=-1 and fieldid=31 and a.id=$24729$ ")

部门经理(部门为条件)
doFieldSQL("select [dbo].[Func_2XL_SplitHrmResourceLastname](jl,‘,‘) from Matrixtable_7 where bm=$25595$")

经理级以上安全级别(申请人为条件)
doFieldSQL(" select dbo.[FUN_2XL_GetSeclevel]($11396$,100),dbo.[FUN_2XL_GetSeclevel]($11396$,100) from systemset")

经理(条件:多人力资源)
select [dbo].[FUN_2GJ_GetHrmResourceByJl1](‘$11396$‘)

用工号带出的(员工调职申请单*特殊*):
1.姓名
doFieldSQL("select lastname,id from HrmResource where workcode = ‘$6318$‘")

2.入职日期
doFieldSQL(" select b.field1,b.field1
from HrmResource a
left join (select id,field1,field30,field31 from cus_fielddata where scopeid = -1) b on a.id = b.id
where a.id = $6319$ ")

3.学历
doFieldSQL(" select e.name,a.educationlevel from HrmResource a left join HrmEducationLevel e on a.educationlevel = e.id where a.id = $6319$")

4.部门
doFieldSQL(" select departmentname,departmentid from HrmResource hrm,HrmDepartment dept where hrm.departmentid = dept.id and hrm.id =$6319$")

5.分部
doFieldSQL(" select a.subcompanyname,a.id from HrmSubCompany a inner join HrmDepartment b on a.id=b.subcompanyid1 where b.id=$6322$ ")

6.岗位名称
doFieldSQL(" select title.jobtitlename,hrm.jobtitle from HrmResource hrm,HrmJobTitles title where hrm.jobtitle = title.id and hrm.id = $6319$ ")

7.直接上级
doFieldSQL(" select hrm2.lastname,hrm2.id from HrmResource hrm1,HrmResource hrm2 where hrm1.managerid = hrm2.id and hrm1.id = $6319$")

8.身份类别
doFieldSQL("select
(select jobgroupname from HrmJobGroups a1 where id = (select c1.jobgroupid from HrmJobActivities c1 where c1.id = (select b1.jobactivityid from HrmJobTitles b1 where a.jobtitle = b1.id))),
(select jobgroupname from HrmJobGroups a1 where id = (select c1.jobgroupid from HrmJobActivities c1 where c1.id = (select b1.jobactivityid from HrmJobTitles b1 where a.jobtitle = b1.id)))
From HrmResource a where a.id = $6319$ ")

9.职级
doFieldSQL(" select b.joblevel,b.joblevel from HrmResource a inner join HrmJobTitles b on a.jobtitle = b.id where a.id = $6319$ ")

10.用工类型
doFieldSQL(" select
case a.field30 when 0 then ‘正式‘ when 1 then ‘试用‘ when 2 then ‘实习‘ when 3 then ‘临时‘ when 4 then ‘食堂‘ when 5 then ‘残障‘ when 6 then ‘退休返聘‘ end,
case a.field30 when 0 then ‘正式‘ when 1 then ‘试用‘ when 2 then ‘实习‘ when 3 then ‘临时‘ when 4 then ‘食堂‘ when 5 then ‘残障‘ when 6 then ‘退休返聘‘ end
from cus_fielddata a
where a.scopeid = -1 and a.id =$6319$ ")

11.是否满足期限
doFieldSQL(" select dbo.Func_2YT_YGKZZRQ(‘$6320$‘) from systemset ")

 

竞业限制协议原因(姓名带出)
doFieldSQL("select yy from uf_jyxzxy where xm=$26858$")

所属公司编号(多人力资源)
doFieldSQL("select distinct subcompanyid1 from HrmResource where id in ($10213$)")

7%转换成0.07并与一个数相乘(咕咕机返利单)
doFieldSQL("select CAST($25090$*replace(‘$25093$‘,‘%‘,‘‘)/100 as DECIMAL(13,2))")

组别(姓名带出)
doFieldSQL(" select dbo.FUN_2XL_GetDepartment(a.departmentid,6) ‘部门‘,dbo.FUN_2XL_GetDepartment(a.departmentid,6) ‘部门‘ from HrmResource a where a.id = $7369$ ")

入职时间(姓名带出)
doFieldSQL(" select b.field1,b.field1
from HrmResource a
left join (select id,field1,field30,field31 from cus_fielddata where scopeid = -1) b on a.id = b.id
where a.id =$7369$ ")


项目类流程:
项目经理:
doFieldSQL("select zrpm from formtable_main_114 where id=$10280$ ")

BU项目主管:
doFieldSQL("select coadjutant from HrmDepartment where id=(select dbo.FUN_2XL_GetDepartmentID(departmentid,3) from HrmResource where id=$21934$) ")

insut门禁 OR (likego)索赔确认签呈
不同门禁对应不同的负责主管,然后需要主管审批
doFieldSQL("select case $30843$ when 0 then ‘傅燕芳‘ 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 ‘吴小龙‘ when 13 then ‘吴小龙‘ when 14 then ‘吴小龙‘ when 15 then ‘吴小龙‘ when 16 then ‘王鹏‘ when 17 then ‘黄沉瑶‘ end,case $30843$ when 0 then ‘9150‘ when 1 then ‘9150‘ when 2 then ‘9150‘ when 3 then ‘9150‘ when 4 then ‘2367‘ when 5 then ‘2367‘ when 6 then ‘2367‘ when 7 then ‘2367‘ when 8 then ‘2367‘ when 9 then ‘2367‘ when 10 then ‘2367‘ when 11 then ‘2367‘ when 12 then ‘2367‘ when 13 then ‘2367‘ when 14 then ‘2367‘ when 15 then ‘2367‘ when 16 then ‘2044‘ when 17 then ‘2050‘ end from systemset ")


判断两个字段是否一致
doFieldSQL("select case when $10264$=$30700$ then ‘是‘ else ‘否‘ end")


出错:
工号带出姓名
doFieldSQL(" select lastname,id from HrmResource where workcode = ‘$6774$‘ and status!=5 and subcompanyid1=$6730$")

工号带出部门出错
doFieldSQL(" select departmentname,departmentid from HrmResource hrm,HrmDepartment dept where hrm.departmentid = dept.id and hrm.status<>5 and hrm.workcode =‘$6774$‘ and hrm.subcompanyid1=$6730$")

 

项目名称是多选,然后带出项目名称:
doFieldSQL("select xmmc from formtable_main_114 where id in ($40166$) ")
项目名称多选,取出第一个选择的项目名称:07.03新产品TDE测试方案审核
步骤一:字段一处取值:doFieldSQL("select ‘$40166$‘")
步骤二:字段二处取值:doFieldSQL("select xmmc from formtable_main_114 where id=dbo.oa_gj_firstid(‘$40166$‘)")

 

共用下拉框字段在某一个流程不显示其中一个选项(主表)
document.getElementById("field11731").options[3] = null;

选择某个部门之后下级部门也可以生效(多级部门)(OA流程新增变更单,影响相关部门)
doFieldSQL("select [dbo].FUN_2GJ_GetHrmdepartmentallbm(‘$18785$‘)")

根据身份证带出所属公司编号(宾馆流程)
select subcompanyid1
from HrmResource h
inner join cus_fielddata c on c.id=h.id and c.scopeid=1
inner join cus_fielddata c1 on c1.id=h.id and c1.scopeid=-1 and c1.field43=0
where status!=5 and c.field33=‘$41988$‘ and ‘$41988$‘!=‘‘

明细表中的下拉框字段取值(宾馆流程)
select count(*) from formtable_main_994_dt1 f1
inner join formtable_main_994 f on f1.mainid=f.id and f1.mx_fjlx=0
inner join workflow_requestbase w on w.requestid=f.requestId and w.currentnodetype not in (0,3)
workflow_requestbase流程状态表
currentnodetype当前节点的操作情况,0表示保存,3表示归档,1、2表示正在跑的流程

 

泛微OA使用到的SQL语句

上一篇:centOS7.7安装MySQL教程


下一篇:MySQL OOM