ccDangJianExamForOpenGauss/target/classes/Sql/teacher.sql

#namespace("teacher")

  -- 通过部门ID获取教师列表
  #sql("getTeacherListByOrgId")
      SELECT
        t1.person_id,
        t1.person_name,
        t1.login_name,
        coalesce(t1.xb, '1') AS xb,
        t1.original_pwd,
        t1.pwd,
        (select count(*) as c from t_transfer_apply as t2  where t2.person_id=t1.person_id and t2.status_id=1 and t2.b_use=1)
        as apply_status
      FROM
        t_sys_loginperson as t1
      WHERE
        t1.b_use = 1
      AND t1.identity_id = 5
      AND t1.org_id = #para(0)  order by t1.sort_id
  #end

   -- 通过单位ID获取教师列表
  #sql("getTeacherListByBureauId")
      SELECT
        t1.person_id,
        t1.person_name,
        t1.login_name,
        coalesce(t1.xb, '1') AS xb,
        t1.original_pwd,
        t1.pwd,
         (select count(*) as c from t_transfer_apply as t2  where t2.person_id=t1.person_id and t2.status_id=1 and t2.b_use=1)
        as apply_status
      FROM
        t_sys_loginperson as t1
      WHERE
        t1.b_use = 1
      AND t1.identity_id = 5
      AND t1.bureau_id = #para(0)  order by t1.sort_id
  #end

  -- 按教师姓名获取教师列表
  #sql("getTeacherListByOrgIdPersonName")
      SELECT
        t1.person_id,
        t1.person_name,
        t1.login_name,
        coalesce(t1.xb, '1') AS xb,
        t1.original_pwd,
        t1.pwd,
         (select count(*) as c from t_transfer_apply as t2  where t2.person_id=t1.person_id and t2.status_id=1 and t2.b_use=1)
        as apply_status
      FROM
        t_sys_loginperson as t1
      WHERE
        t1.b_use = 1
      AND t1.identity_id = 5
      AND t1.org_id = #para(0)
      AND t1.person_name LIKE concat('%',#para(1),'%') order by t1.sort_id
  #end

  -- 按教师姓名,通过单位 获取教师列表
  #sql("getTeacherListByPersonNameAndBureauId")
      SELECT
        t1.person_id,
        t1.person_name,
        t1.login_name,
        coalesce(t1.xb, '1') AS xb,
        t1.original_pwd,
        t1.pwd,
         (select count(*) as c from t_transfer_apply as t2  where t2.person_id=t1.person_id and t2.status_id=1 and t2.b_use=1)
        as apply_status
      FROM
        t_sys_loginperson as t1
      WHERE
        t1.b_use = 1
      AND t1.identity_id = 5
      AND t1.bureau_id = #para(0)
      AND t1.person_name LIKE concat('%',#para(1),'%') order by t1.sort_id
  #end

  -- 更改人员的部门
  #sql("changeOrgByPersonIdAndOrgId")
    update t_sys_loginperson set org_id=?,operator=?,ip_address=? where person_id=?
  #end

  --删除指定人员的职务与分管工作
  #sql("deleteDutuesAndChargeByPersonId")
    update t_person_duty_charge set b_use=0,operator=?,ip_address=? where person_id=?
  #end
#end