泛微表单上一些常用的插入SQL操作
- 工号:
doFieldSQL("select loginid from hrmresource t where t.id=$50798$ ")
- 直接上级:
doFieldSQL(" select t1.lastname,t1.id from hrmresource t left join hrmresource t1 on t.managerid=t1.id where t.id=$50798$ ")
- 间接上级:
doFieldSQL(" select t2.lastname,t2.id from hrmresource t left join hrmresource t1 on t.managerid=t1.id left join hrmresource t2 on t1.managerid=t2.id where t.id = $50798$ ")
- 系统负责人:
doFieldSQL(" select hs.lastname,hs.id from Matrixtable_3 mt,hrmresource hs where mt.xtfzr = hs.id and mt.ssxt= $50800$ ")
- 根据部门ID获取部门负责人:
doFieldSQL(" select c.lastname,c.id from hrmdepartment a left join hrmdepartmentdefined b on a.id = b.deptid left join HrmResource c on
convert(int,convert(varchar(20),b.bmfzr))=c.id where a.id = $60896$")
- 责任部门:
doFieldSQL("select departmentname,departmentid from HrmResource a LEFT JOIN HrmDepartment b on a.departmentid=b.id where a.id=$57579$ ")
- 岗位:
doFieldSQL("select t.jobtitlename,t.id from hrmjobtitles t left join hrmresource t1 on t.id=t1.jobtitle where t1.id=$57579$ ")
- 责任分部:
doFieldSQL("select subcompanyname,subcompanyid1 from HrmResource a LEFT JOIN hrmsubcompany b on a.subcompanyid1=b.id where a.id=$57579$ ")
(子公司没有分部的概念,因为子公司是放在总部下面的,所以子公司本身就是一个分部,而其下面的物流中心、品质中心、财务部等本来是分部的等级,在OA中是部门形式,因此需要特殊处理,这条sql需要注意supdepid = 28这里,28为浙江子公司的部门ID)
- 根据申请人获取子公司分部:
doFieldSQL(" with CTE as (select departmentname,id,supdepid from hrmdepartment where id = (select departmentid from HrmResource where id = $58415$)
UNION ALL select a.departmentname,a.id,a.supdepid from hrmdepartment a inner JOIN CTE b on a.id=b.supdepid )select departmentname,id from CTE
where id in (select id from hrmdepartment where supdepid = 28 and canceled <> 1) ")
- 间接上级有则取间接上级,没有则取直接上级:
doFieldSQL(" select (case when t2.lastname is null then t1.lastname else t2.lastname end) as lastname,(case when t2.id is null then t1.id else t2.id end) as id from hrmresource t left join hrmresource t1 on t.managerid=t1.id left join hrmresource t2 on t1.managerid=t2.id where t.id = $59676$ ")
- 两个人力资源合并成一个多人力资源:
update formtable_main_64 set jjryzzsj = convert(varchar(20),zjsj)+','+convert(varchar(20),jjr) where requestid=$requestid$
- 根据申请人去动态获取上级领导或者上上级领导:
with CTE as (select lastname,id,managerid from hrmresource where id = 2422 UNION ALL select a.lastname,a.id,a.managerid from hrmresource a inner JOIN CTE b on a.id=b.managerid )select lastname,id from CTE option (maxrecursion 5) --2422是申请人ID maxrecursion 5 哪里的5是递归层级。(如:你想取申请人的直接上级,就填1;想取申请人的上上级,就填2) --它会把申请的人所有上级全部查询出来,用order by 排序,然后用top 1 去取