gogozz

导航

泛微表单上一些常用的插入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 去取  

 

posted on 2023-09-06 14:34  stfzhuang  阅读(487)  评论(0编辑  收藏  举报