#namespace("studentYd")

  -- 获取学生状态列表
  #sql("get_dm_status_student")
    select status_code,status_name,b_use,change_person_b_use,is_show from t_dm_status_student where b_use=1 and is_show=1
  #end

   -- 获取学生状态列表ByCode
  #sql("get_dm_status_student_by_code")
    select status_code,status_name,b_use,change_person_b_use,is_show from t_dm_status_student where b_use=1 and is_show=1 and status_code=?
  #end

  -- 修改人员主表的人员状态
  #sql("updateStudentStatus")
    update t_sys_loginperson set b_use=?,status_code=?,operator=?,ip_address=? where person_id=?
  #end


  -- 判断一个学生是不是可以申请调转
  #sql("checkAllowStudentTransferApply")
    select    t1.id,t1.source_bureau_id,t1.target_bureau_id,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.source_bureau_id) as source_bureau_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.target_bureau_id) as target_bureau_name,
             t1.identity_id,t1.person_id,t1.person_name,t1.status_id,t1.target_is_read,t1.source_is_read,t1.apply_time,
             t1.apply_message,t1.echo_time,t1.echo_message,t1.b_use
     from t_transfer_apply as t1 where t1.b_use=1 and t1.person_id=? order by t1.apply_time desc
  #end

  -- 获取学生调动申请列表
  #sql("getStudentTransferApplyList")
    select   t1.id,t1.source_bureau_id,t1.target_bureau_id,
             (select t2.class_name from t_base_class as t2 where t2.class_id=(select t3.s_class_id from t_sys_loginperson as t3 where t3.person_id=t1.person_id)) as class_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.source_bureau_id) as source_bureau_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.target_bureau_id) as target_bureau_name,
             t1.identity_id,t1.person_id,t1.person_name,t1.status_id,t1.target_is_read,t1.source_is_read,t1.apply_time,
             t1.apply_message,t1.echo_time,t1.echo_message,t1.b_use
     from t_transfer_apply as t1 where t1.source_bureau_id=#para(0) and t1.person_name LIKE concat('%',#para(1),'%')
            and t1.b_use=1 and t1.identity_id=6  order by t1.apply_time desc
  #end

  -- 获取学生调动反馈列表
  #sql("getStudentTransferEchoList")
    select t1.id,t1.source_bureau_id,t1.target_bureau_id,
             (select t2.class_name from t_base_class as t2 where t2.class_id=(select t3.s_class_id from t_sys_loginperson as t3 where t3.person_id=t1.person_id)) as class_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.source_bureau_id) as source_bureau_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.target_bureau_id) as target_bureau_name,
             t1.identity_id,t1.person_id,t1.person_name,t1.status_id,t1.target_is_read,t1.source_is_read,t1.apply_time,
             t1.apply_message,t1.echo_time,t1.echo_message,t1.b_use
     from t_transfer_apply as t1 where t1.target_bureau_id=#para(0)
     and t1.person_name LIKE concat('%',#para(1),'%') and t1.b_use=1 and t1.identity_id=6
     order by t1.apply_time desc
  #end

  -- 获取指定ID的学生调转申请
  #sql("getStudentTransferInfoById")
      select t1.id,t1.source_bureau_id,t1.target_bureau_id,
             (select t2.class_name from t_base_class as t2 where t2.class_id=(select t3.s_class_id from t_sys_loginperson as t3 where t3.person_id=t1.person_id)) as class_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.source_bureau_id) as source_bureau_name,
             (select t2.org_name from t_base_organization as t2 where t2.org_id=t1.target_bureau_id) as target_bureau_name,
             t1.identity_id,t1.person_id,t1.person_name,t1.status_id,t1.target_is_read,t1.source_is_read,t1.apply_time,
             t1.apply_message,t1.echo_time,t1.echo_message,t1.b_use
      from t_transfer_apply as t1 where id=?
  #end

  -- 删除指定的调转ID
  #sql("deleteTransferInfoById")
    update t_transfer_apply set b_use=0,operator=?,ip_address=? where id=?
  #end

  -- 获取学生调转申请的未读取个数
  #sql("getNewStudentTransferApplyCount")
    select count(1) as c from t_transfer_apply where b_use=1 and target_is_read=0 and status_id=1
    and target_bureau_id=? and identity_id=6
  #end

  -- 教师调转提醒(收到新的回执)
  #sql("getNewStudentTransferEchoCount")
    select count(1) as c from t_transfer_apply where b_use=1 and source_is_read=0 and status_id>1
    and source_bureau_id=? and identity_id=6
  #end

  -- 调转的三种状态
  #sql("getTransferApplyStatus")
    select apply_status_id,apply_status_name,for_check from t_transfer_apply_status where for_check=1
  #end

    --判断一个人是不是处于待审核状态
  #sql("getPersonStatus")
      select count(*) as c from t_transfer_apply where person_id=? and b_use=1 and status_id=1
  #end
#end