<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.srs.comprehensive.mapper.CphSqlServerDataMapper">

    <!-- 学生信息-->
    <select id="getStudentInfoList" resultType="java.util.Map">
        select xsid,ss.bjid, xh, xm, xb, sfzh, csrq, hkxz, mz, jg, jtdz, gb,
               zzmm, rxrq, byxx, ksh, zjlx,bc.bjdm,bc.bjmc,ss.mark as sfzx
        from Stu_Student ss left join BD_Class bc on ss.bjid=bc.bjid
        where ss.rxrq &gt; '2019-08-01' and ss.xh is not null
        <if test="stuNo != null  and stuNo != ''">and xh=#{stuNo}</if>
        order by xsid
        offset ((${startPage} -1) * ${pageSize}) rows
        fetch next ${pageSize} rows only
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>

    <select id="selectNumber" resultType="int">
        select count(1) as number from Stu_Student
    </select>

    <!-- 班级信息分页-->
    <select id="getClassInfoListPage" resultType="java.util.Map" parameterType="PageDto">
        select  BJID,bjmc,bjdm,zyid,njid,bjjc  from  BD_Class
        where BJID not in
        (select top (#{pageNum}) bjid from BD_Class order by bjid)
        order by BJID  <!--offset 10 rows fetch next 10 rows only-->
    </select>

    <!-- 班级信息-->
    <select id="getClassInfoList" resultType="java.util.Map">
        select  BJID,bjmc,bjdm,zyid,njid,bjjc  from  BD_Class  order by bjid  <!-- where BJDM like '2024%'  offset 10 rows fetch next 10 rows only-->
    </select>

    <!-- 班级信息关键查询-->
    <select id="getClassInfodm" parameterType="string" resultType="CphClassTbsj">
        select  BJID,bjmc,bjdm,zyid,njid,bjjc  from  BD_Class
        where bjdm=#{bjdm}
    </select>


    <!-- 年级信息-->
    <select id="getGradeInfoList" resultType="java.util.Map">
        select njid,njdm,njmc,rxnf,status  from  BD_grade
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>

<!--使用年级id查询年级代码-->
    <select id="getnjid" resultType="string">
        select njdm from BD_grade where njid=#{njid}
    </select>

    <!-- 专业信息分页-->
    <select id="getSpecialtyInfoListPage" resultType="java.util.Map" parameterType="PageDto">
        select zyid, zydm, zymc, zyjc, ds.status, df.yxid, df.yxmc, df.yxdm
        from BD_Specialty ds left join BD_Faculty df on ds.YXID= df.yxid
        where zyid not in
        (select top (#{pageNum}) zyid from ds order by zyid)
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>

    <!-- 专业信息-->
    <select id="getSpecialtyInfoList" resultType="java.util.Map">
        select zyid, zydm, zymc, zyjc, ds.status, df.yxid, df.yxmc, df.yxdm
        from BD_Specialty ds left join BD_Faculty df on ds.YXID= df.yxid
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>

    <!-- 专业代码查询-->
    <select id="getSpecialtyzydm" parameterType="string" resultType="java.util.Map">
        select zyid, zydm, zymc, zyjc, ds.status, df.yxid, df.yxmc, df.yxdm
        from BD_Specialty ds left join BD_Faculty df on ds.YXID= df.yxid
        where zydm=#{zydm}
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>

    <select id="getzydm" resultType="string">
        select zydm from BD_Specialty where zyid=#{zyid}
    </select>

    <!-- 班级绑辅导员信息-->
    <select id="getBZRClassInfoList" resultType="java.util.Map">
        select bjid,bjdm, bjmc, xm, zgh, ClassType, StuNum
        from V_BZRClass
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>
    <select id="selectClassBZR" resultType="string">
        select VBC.zgh
        from V_BZRClass as VBC where bjdm=#{bjdm}
    </select>

    <!-- 职工信息-->
    <select id="getEmployEEInfoList" resultType="java.util.Map">
        select el.emid,el.zgh, el.xm, el.xb, el.userid, tt.ksh, tt.ksmc, tt.ksid,df.yxid, df.yxmc, df.yxdm
        from BD_Employee el
            left join  BD_Department tt on el.KSID=tt.KSID
            left join  BD_Faculty df on el.yxid=df.yxid
        <!-- order by xsid  offset 10 rows fetch next 10 rows only-->
    </select>


</mapper>
