<?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.stureg.mapper.SrsStuRegMapper">

    <resultMap id="ExistKsh" type="SrsStuReg">
        <result property="KSH" column="KSH"/>
    </resultMap>

    <resultMap type="SrsStuReg" id="SrsStuRegResult">
        <result property="id" column="id"/>
        <result property="XSXM" column="XSXM"/>
        <result property="KSH" column="KSH"/>
        <result property="SFZH" column="SFZH"/>
        <result property="SJH" column="SJH"/>
        <result property="XB" column="XB"/>
        <result property="ZZMM" column="ZZMM"/>
        <result property="MZ" column="MZ"/>
        <result property="XH" column="XH"/>
        <result property="XY" column="XY"/>
        <result property="XYDM" column="XYDM"/>
        <result property="ZY" column="ZY"/>
        <result property="ZYDM" column="ZYDM"/>
        <result property="BJ" column="BJ"/>
        <result property="BJDM" column="BJDM"/>
        <result property="lastSchool" column="last_school"/>
        <result property="lastSchoolAddr" column="last_school_addr"/>
        <result property="famName" column="fam_name"/>
        <result property="famRelation" column="fam_relation"/>
        <result property="famPhone" column="fam_phone"/>
        <result property="famNowAddr" column="fam_now_addr"/>
        <result property="sfzAddr" column="sfz_addr"/>
        <result property="regStatus" column="reg_status"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="delFlag" column="del_flag"/>
        <result property="isChangeSJHM" column="is_change_SJHM"/>
        <result property="isChangePwd" column="is_change_pwd"/>
        <result property="dorm" column="dorm"/>
        <result property="isLoan" column="is_loan"/>
        <result property="isDirection" column="is_direction"/>
        <result property="loanNo" column="loan_no"/>
        <result property="deptId"    column="dept_id"    />
        <result property="majorId"    column="major_id"    />
        <result property="classId"    column="class_id"    />
        <result property="isRetired" column="is_retired"/>
        <result property="isFiling"    column="is_filing"    />
        <result property="isDisability"    column="is_disability"    />
        <result property="disableDescript"    column="disable_descript"    />
    </resultMap>

    <sql id="selectSrsStuRegVo">
        select a.*,
               case c.room_no is NULL
                   when true then "无"
                   else concat(c.park_name,' ',c.building_name,' ',c.room_no) end as dorm
        from srs_stu_reg as a
                 left join dms_new_reg as b on a.KSH = b.ksh
                 left join view_dorm_info as c on b.dormitory_id = c.id
    </sql>

    <select id="selectSrsStuRegList" parameterType="SrsStuReg" resultMap="SrsStuRegResult">
        <include refid="selectSrsStuRegVo"/>
        <where>
            <if test="XSXM != null  and XSXM != ''">and a.XSXM = #{XSXM}</if>
            <if test="KSH != null  and KSH != ''">and a.KSH = #{KSH}</if>
            <if test="SFZH != null  and SFZH != ''">and a.SFZH = #{SFZH}</if>
            <if test="SJH != null  and SJH != ''">and a.SJH = #{SJH}</if>
            <if test="XB != null  and XB != ''">and a.XB = #{XB}</if>
            <if test="ZZMM != null  and ZZMM != ''">and a.ZZMM = #{ZZMM}</if>
            <if test="MZ != null  and MZ != ''">and a.MZ = #{MZ}</if>
            <if test="XH != null  and XH != ''">and a.XH = #{XH}</if>
            <if test="XY != null  and XY != ''">and a.XY = #{XY}</if>
            <if test="XYDM != null  and XYDM != ''">and a.XYDM = #{XYDM}</if>
            <if test="ZY != null  and ZY != ''">and a.ZY = #{ZY}</if>
            <if test="ZYDM != null  and ZYDM != ''">and a.ZYDM = #{ZYDM}</if>
            <if test="BJ != null  and BJ != ''">and a.BJ = #{BJ}</if>
            <if test="BJDM != null  and BJDM != ''">and a.BJDM = #{BJDM}</if>
            <if test="lastSchool != null  and lastSchool != ''">and a.last_school = #{lastSchool}</if>
            <if test="lastSchoolAddr != null  and lastSchoolAddr != ''">and a.last_school_addr = #{lastSchoolAddr}</if>
            <if test="famName != null  and famName != ''">and a.fam_name like concat('%', #{famName}, '%')</if>
            <if test="famRelation != null  and famRelation != ''">and a.fam_relation = #{famRelation}</if>
            <if test="famPhone != null  and famPhone != ''">and a.fam_phone = #{famPhone}</if>
            <if test="famNowAddr != null  and famNowAddr != ''">and a.fam_now_addr = #{famNowAddr}</if>
            <if test="sfzAddr != null  and sfzAddr != ''">and a.sfz_addr = #{sfzAddr}</if>
            <if test="regStatus != null  and regStatus != ''">and a.reg_status = #{regStatus}</if>
            <if test="delFlag != null  and delFlag != ''">and a.del_flag= #{delFlag}</if>
            <if test="isLoan != null  and isLoan != ''">and a.is_loan = #{isLoan}</if>
            <if test="isDirection != null  and isDirection != ''">and a.is_direction= #{isDirection}</if>
            <if test="isChangePwd != null  and isChangePwd != ''">and a.is_change_pwd= #{isChangePwd}</if>
        </where>
    </select>

    <select id="selectSrsStuRegById" parameterType="Long" resultMap="SrsStuRegResult">
        <include refid="selectSrsStuRegVo"/>
        where a.id = #{id}
    </select>
    <select id="selectSrsStuRegByStuNo" parameterType="String" resultMap="SrsStuRegResult">
        <include refid="selectSrsStuRegVo"/>
        where a.XH = #{stuNo}
    </select>

    <select id="getOwnInfoByKsh" parameterType="String" resultMap="SrsStuRegResult">
        <include refid="selectSrsStuRegVo"/>
        where a.KSH = #{ksh}
    </select>
    <select id="getDmsClass" resultType="com.srs.stureg.domain.VO.TeacherVo">
        SELECT a.id,a.KSH,a.BJDM,a.BJ,c.`name` as TeacherName,c.employee_id as Tno
        from srs_stu_reg as a
                 left join srs_class as b on a.BJDM=b.class_code
                 left join cph_teacher as c on b.teacher_id=c.teacher_id
        <where>
            <if test="Tno != null  and Tno != ''">and c.employee_id = #{Tno} </if>
            <if test="KSH != null  and KSH != ''">and a.KSH = #{KSH}</if>
        </where>
    </select>
    <select id="getDmsClassList" resultType="com.srs.stureg.domain.VO.TeacherVo">
        SELECT b.id,concat( b.park_name,' ',b.building_name,' ',b.room_no ,'(',b.gender,')') as dorm,a.ksh as KSH,c.XSXM,c.BJ
        from dms_new_reg as a
        left join view_dorm_info as b on b.id=a.dormitory_id
        left join srs_stu_reg as c on a.ksh=c.KSH
        left join srs_class as d on c.BJDM=d.class_code
        left join cph_teacher as e on e.teacher_id=d.teacher_id
        <where>
            <if test="Tno != null  and Tno != ''">and e.employee_id = #{Tno} </if>
        </where>
    </select>
    <select id="selectIsExistKsh" resultMap="ExistKsh">
        select KSH from srs_stu_reg
        where KSH in
        <foreach collection="array" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

    <select id="selectCountByRegStatus" resultType="java.lang.Integer">
        select count(*)
        from srs_stu_reg
        where reg_status = #{regStatus}
    </select>

    <insert id="insertSrsStuReg" parameterType="SrsStuReg" useGeneratedKeys="true" keyProperty="id">
        insert into srs_stu_reg
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="XSXM != null">XSXM,</if>
            <if test="KSH != null">KSH,</if>
            <if test="SFZH != null">SFZH,</if>
            <if test="SJH != null">SJH,</if>
            <if test="XB != null">XB,</if>
            <if test="ZZMM != null">ZZMM,</if>
            <if test="MZ != null">MZ,</if>
            <if test="XH != null">XH,</if>
            <if test="XY != null">XY,</if>
            <if test="XYDM != null">XYDM,</if>
            <if test="ZY != null">ZY,</if>
            <if test="ZYDM != null">ZYDM,</if>
            <if test="BJ != null">BJ,</if>
            <if test="BJDM != null">BJDM,</if>
            <if test="lastSchool != null">last_school,</if>
            <if test="lastSchoolAddr != null">last_school_addr,</if>
            <if test="famName != null">fam_name,</if>
            <if test="famRelation != null">fam_relation,</if>
            <if test="famPhone != null">fam_phone,</if>
            <if test="famNowAddr != null">fam_now_addr,</if>
            <if test="sfzAddr != null">sfz_addr,</if>
            <if test="regStatus != null">reg_status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="isChangePwd != null">is_change_pwd,</if>
            <if test="isLoan != null">is_loan,</if>
            <if test="isDirection != null">is_direction,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="majorId != null">major_id,</if>
            <if test="classId != null">class_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="XSXM != null">#{XSXM},</if>
            <if test="KSH != null">#{KSH},</if>
            <if test="SFZH != null">#{SFZH},</if>
            <if test="SJH != null">#{SJH},</if>
            <if test="XB != null">#{XB},</if>
            <if test="ZZMM != null">#{ZZMM},</if>
            <if test="MZ != null">#{MZ},</if>
            <if test="XH != null">#{XH},</if>
            <if test="XY != null">#{XY},</if>
            <if test="XYDM != null">#{XYDM},</if>
            <if test="ZY != null">#{ZY},</if>
            <if test="ZYDM != null">#{ZYDM},</if>
            <if test="BJ != null">#{BJ},</if>
            <if test="BJDM != null">#{BJDM},</if>
            <if test="lastSchool != null">#{lastSchool},</if>
            <if test="lastSchoolAddr != null">#{lastSchoolAddr},</if>
            <if test="famName != null">#{famName},</if>
            <if test="famRelation != null">#{famRelation},</if>
            <if test="famPhone != null">#{famPhone},</if>
            <if test="famNowAddr != null">#{famNowAddr},</if>
            <if test="sfzAddr != null">#{sfzAddr},</if>
            <if test="regStatus != null">#{regStatus},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="isChangePwd != null">#{isChangePwd},</if>
            <if test="isLoan != null">#{isLoan},</if>
            <if test="isDirection != null">#{isDirection},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="majorId != null">#{majorId},</if>
            <if test="classId != null">#{classId},</if>
        </trim>
    </insert>

    <update id="updateSrsStuReg" parameterType="SrsStuReg">
        update srs_stu_reg
        <trim prefix="SET" suffixOverrides=",">
            <if test="XSXM != null">XSXM = #{XSXM},</if>
            <if test="KSH != null">KSH = #{KSH},</if>
            <if test="SFZH != null">SFZH = #{SFZH},</if>
            <if test="SJH != null">SJH = #{SJH},</if>
            <if test="XB != null">XB = #{XB},</if>
            <if test="ZZMM != null">ZZMM = #{ZZMM},</if>
            <if test="MZ != null">MZ = #{MZ},</if>
            <if test="XH != null">XH = #{XH},</if>
            <if test="XY != null">XY = #{XY},</if>
            <if test="XYDM != null">XYDM = #{XYDM},</if>
            <if test="ZY != null">ZY = #{ZY},</if>
            <if test="ZYDM != null">ZYDM = #{ZYDM},</if>
            <if test="BJ != null">BJ = #{BJ},</if>
            <if test="BJDM != null">BJDM = #{BJDM},</if>
            <if test="lastSchool != null">last_school = #{lastSchool},</if>
            <if test="lastSchoolAddr != null">last_school_addr = #{lastSchoolAddr},</if>
            <if test="famName != null">fam_name = #{famName},</if>
            <if test="famRelation != null">fam_relation = #{famRelation},</if>
            <if test="famPhone != null">fam_phone = #{famPhone},</if>
            <if test="famNowAddr != null">fam_now_addr = #{famNowAddr},</if>
            <if test="sfzAddr != null">sfz_addr = #{sfzAddr},</if>
            <if test="regStatus != null">reg_status = #{regStatus},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="isChangeSJHM != null">is_change_SJHM = #{isChangeSJHM},</if>
            <if test="isChangePwd != null">is_change_pwd = #{isChangePwd},</if>
            <if test="isLoan != null">is_loan = #{isLoan},</if>
            <if test="isDirection != null">is_direction = #{isDirection},</if>
            <if test="loanNo != null">loan_no = #{loanNo},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="majorId != null">major_id = #{majorId},</if>
            <if test="classId != null">class_id = #{classId},</if>
            <if test="isRetired != null">is_retired = #{isRetired},</if>
            <if test="isFiling != null">is_filing = #{isFiling},</if>
            <if test="isDisability != null">is_disability = #{isDisability},</if>
            <if test="disableDescript != null">disable_descript = #{disableDescript},</if>
            <if test="isSmoke != null">is_smoke = #{isSmoke},</if>
            <if test="isEarly != null">is_early = #{isEarly},</if>
            <if test="hobby != null">hobby = #{hobby},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateSrsStuRegByKSH" parameterType="SrsStuReg">
        update srs_stu_reg
        <trim prefix="SET" suffixOverrides=",">
            <if test="SJH != null">SJH = #{SJH},</if>
            <if test="famName != null">fam_name = #{famName},</if>
            <if test="famPhone != null">fam_phone = #{famPhone},</if>
            <if test="famNowAddr != null">fam_now_addr = #{famNowAddr},</if>
            <if test="sfzAddr != null">sfz_addr = #{sfzAddr},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="isRetired != null">is_retired = #{isRetired},</if>
            <if test="isFiling != null">is_filing = #{isFiling},</if>
            <if test="isChangeSJHM != null">is_change_SJHM = #{isChangeSJHM},</if>
            <if test="isDisability != null">is_disability = #{isDisability},</if>
            <if test="disableDescript != null">disable_descript = #{disableDescript},</if>
        </trim>
        where KSH = #{ksh}
    </update>

    <delete id="deleteSrsStuRegById" parameterType="Long">
        delete
        from srs_stu_reg
        where id = #{id}
    </delete>

    <delete id="deleteSrsStuRegByIds" parameterType="String">
        delete from srs_stu_reg where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!--    逻辑删除-->
    <update id="deleteSrsStuRegByIdR">
        update srs_stu_reg set del_flag=2 where id in
        <foreach collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
    <!--判断表存在-->
    <select id="isTableExist" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_name = #{tableName}
    </select>
    <!--创建表-->
    <update id="createTable" parameterType="java.util.Map">
        CREATE TABLE ${tableName}
        (
        id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键自增',
        <foreach collection="tabList" separator="," item="v" index="key">
            ${key} ${v} null
        </foreach>
        )
        ENGINE InnoDB AUTO_INCREMENT=1;
    </update>
    <!--    动态导入dbf-->
    <insert id="insertData" parameterType="java.util.List">
        <foreach collection="dataList" item="item" index="index" separator=";">
            INSERT INTO ${tableName}
            (
            <foreach collection="item" index="key" item="value" separator=",">
                <if test="value!=null">
                    ${key}
                </if>
            </foreach>
            )
            values
            (
            <foreach collection="item" index="key" item="value" separator=",">
                <if test="value!=null">
                    #{value}
                </if>
            </foreach>
            )
            # ON DUPLICATE KEY UPDATE
            # s_achievement = VALUES(s_achievement)
        </foreach>
    </insert>
    <insert id="insertAll" parameterType="java.util.List">
        insert into srs_stu_reg(
        XH,XSXM,SFZH,SJH,XB,ZZMM,MZ,XY,XYDM,ZY,ZYDM,BJ,BJDM,create_time,create_by
        )
        values
        <foreach collection="list" item="item" index="i" separator=",">
            (#{item.XH},#{item.XSXM},#{item.SFZH},#{item.SJH},#{item.XB}
            ,#{item.ZZMM},#{item.MZ},#{item.XY},#{item.XYDM},#{item.ZY}
            ,#{item.ZYDM},#{item.BJ},#{item.BJDM},#{item.createTime},#{item.createBy})
        </foreach>
    </insert>
    <update id="updateSrsStuRegList" parameterType="java.util.List">
        <foreach collection="srsStuReg" item="item" index="index" open="" close="" separator=";">
            update srs_stu_reg
            <trim prefix="SET" suffixOverrides=",">
                <if test="XSXM != null">XSXM = #{item.XSXM},</if>
                <if test="KSH != null">KSH = #{item.KSH},</if>
                <if test="SFZH != null">SFZH = #{item.SFZH},</if>
                <if test="SJH != null">SJH = #{item.SJH},</if>
                <if test="XB != null">XB = #{item.XB},</if>
                <if test="ZZMM != null">ZZMM = #{item.ZZMM},</if>
                <if test="MZ != null">MZ = #{item.MZ},</if>
                <if test="XH != null">XH = #{item.XH},</if>
                <if test="XY != null">XY = #{item.XY},</if>
                <if test="XYDM != null">XYDM = #{item.XYDM},</if>
                <if test="ZY != null">ZY = #{item.ZY},</if>
                <if test="ZYDM != null">ZYDM = #{item.ZYDM},</if>
                <if test="BJ != null">BJ = #{item.BJ},</if>
                <if test="BJDM != null">BJDM = #{item.BJDM},</if>
                <if test="lastSchoolAddr != null">last_school_addr = #{item.lastSchoolAddr},</if>
                <if test="famName != null">fam_name = #{item.famName},</if>
                <if test="famRelation != null">fam_relation = #{item.famRelation},</if>
                <if test="famPhone != null">fam_phone = #{item.famPhone},</if>
                <if test="famNowAddr != null">fam_now_addr = #{item.famNowAddr},</if>
                <if test="sfzAddr != null">sfz_addr = #{item.sfzAddr},</if>
                <if test="regStatus != null">reg_status = #{item.regStatus},</if>
                <if test="createTime != null">create_time = #{item.createTime},</if>
                <if test="createBy != null">create_by = #{item.createBy},</if>
                <if test="updateTime != null">update_time = #{item.updateTime},</if>
                <if test="updateBy != null">update_by = #{item.updateBy},</if>
                <if test="delFlag != null">del_flag = #{item.delFlag},</if>
                <if test="delFlag != null">LQZY = #{item.delFlag},</if>
                <if test="delFlag != null">KLDM = #{item.delFlag},</if>
                <if test="delFlag != null">PCDM = #{item.delFlag},</if>
                <if test="delFlag != null">TDDW = #{item.delFlag},</if>
                <if test="delFlag != null">XZNX = #{item.delFlag},</if>
                <if test="delFlag != null">KLMC = #{item.delFlag},</if>
                <if test="delFlag != null">TDDWMC = #{item.delFlag},</if>
                <if test="delFlag != null">TDCJ = #{item.delFlag},</if>
                <if test="isChangePwd != null">is_change_pwd = #{item.isChangePwd},</if>
                <if test="isLoan != null">is_loan = #{isLoan},</if>
                <if test="isDirection != null">is_direction = #{isDirection},</if>
            </trim>
            where id = #{item.id}
        </foreach>
    </update>
    <!--    查询数据类型-->
    <select id="selectDatabaseType" resultType="java.util.Map">
        SELECT COLUMN_NAME, COLUMN_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'srs'
          AND TABLE_NAME = #{tableName}
    </select>

    <!--    <resultMap id="resultMap" type="java.util.HashMap">-->
    <!--        <result property="key" column="key" />-->
    <!--        <result property="value" column="value" />-->
    <!--    </resultMap>-->

    <!--查询全部-->
    <select id="selectTable" resultType="java.util.HashMap">
        select *
        from ${tableName}
    </select>
    <!--查询条数-->
    <select id="selectTableCount" resultType="int">
        select count(1)
        from ${tableName}
    </select>

    <!--    list修改-->
    <update id="updateMapListAll" parameterType="java.util.List">
        <foreach collection="dataList" item="item" index="index" separator=";">
            update ${tableName}
            <trim prefix="SET" suffixOverrides=",">
                <foreach collection="item" index="key" item="value">
                    <if test="value != null">${key} = #{value},</if>
                </foreach>
            </trim>
            where KSH=#{item.KSH}
        </foreach>
    </update>
    <select id="selectAll" resultType="java.util.HashMap">
        #         SELECT t_tdd.*,t_jhk.zydh,t_jhk.xznx as xz,t_jhk.kldm as kldm_1,t_jhk.zymc,t_jhk.zydm
#         FROM t_tdd left JOIN t_jhk ON t_tdd.lqzy=t_jhk.zydh AND t_tdd.kldm=t_jhk.kldm AND t_tdd.pcdm=t_jhk.pcdm ;
#         LEFT JOIN t_tddw ON t_tdd.tddw=t_tddw.tddwdm AND t_tdd.kldm=t_tddw.kldm AND t_tdd.pcdm=t_tddw.pcdm
#             INTO CURSOR temp1
#         SELECT temp1.*,td_kldm.klmc,zhuanye.zyx as xib,zhuanye.zydh FROM temp1 left JOIN td_kldm
#                                                                                          ON temp1.kldm_1=td_kldm.kldm
#                                                                                LEFT JOIN zhuanye on temp1.zymc=zhuanye.zymc ORDER BY kl.klmc,temp1.zymc,temp1.tdcj
#                 desc INTO CURSOR drk_tmp
#         SELECT distinct * FROM drk_tmp WHERE !ISNULL(zymc) ORDER BY xib,tddwmc,zymc,tdcj INTO CURSOR drk
        SELECT DISTINCT *
        FROM (SELECT t_tdd.*, t_jhk.zydh, t_jhk.xznx AS xz, t_jhk.kldm AS kldm_1, t_jhk.zymc, t_jhk.zydm
              FROM t_tdd
                       LEFT JOIN t_jhk
                                 ON t_tdd.lqzy = t_jhk.zydh AND t_tdd.kldm = t_jhk.kldm AND t_tdd.pcdm = t_jhk.pcdm
                       LEFT JOIN t_tddw ON t_tdd.tddw = t_tddw.tddwdm AND t_tdd.kldm = t_tddw.kldm AND
                                           t_tdd.pcdm = t_tddw.pcdm) AS temp1
                 LEFT JOIN td_kldm ON temp1.kldm_1 = td_kldm.kldm
             -- LEFT JOIN zhuanye ON temp1.zymc = zhuanye.zymc
        WHERE !ISNULL(temp1.zymc)
        ORDER BY temp1.zymc, temp1.tdcj DESC;
    </select>

    <select id="selectTJHK" resultType="java.util.HashMap">
        SELECT DISTINCT *
        FROM (
        SELECT stu.*,
        FROM
        (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.PCDM} AS PCDM,
            #{item.KLDM} AS KLDM,
            #{item.ZYDH} AS ZYDH,
            #{item.ZYMC} AS ZYMC,
            #{item.XZNX} as XZNX,
            #{item.ZYDM} as ZYDM
            FROM DUAL
        </foreach>
        ) as stu
        LEFT JOIN stu
        ON srs_stu_reg.lqzy = stu.ZYDH AND srs_stu_reg.kldm = stu.KLDM AND srs_stu_reg.pcdm = stu.PCDM
        ) AS temp1
        WHERE !ISNULL(temp1.zymc)
        ORDER BY temp1.zymc, temp1.tdcj DESC;
    </select>

    <select id="getStuRegWithList" resultType="java.util.HashMap">
        SELECT DISTINCT *
        FROM (
        SELECT srs_stu_reg.*, t_jhk.*
        FROM srs_stu_reg
        LEFT JOIN (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.ZYDH} AS zydh,
            #{item.XZNX} AS xznx2,
            #{item.KLDM} AS kldm2,
            #{item.ZYMC} AS zymc2,
            #{item.ZYDM} AS zydm3,
            #{item.PCDM} AS pcdm2
            FROM DUAL
        </foreach>
        ) AS t_jhk ON srs_stu_reg.lqzy = t_jhk.zydh AND srs_stu_reg.kldm = t_jhk.kldm2
        AND srs_stu_reg.pcdm = t_jhk.pcdm2
        ) AS temp1
        WHERE temp1.zymc2 IS NOT NULL
        ORDER BY temp1.zymc2, temp1.tdcj DESC;
    </select>

    <select id="selectTTDDW" resultType="java.util.HashMap">
        SELECT DISTINCT *
        FROM (
        SELECT srs_stu_reg.*, t_tddw.*
        FROM srs_stu_reg
        LEFT JOIN (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.PCDM} AS pcdm1,
            #{item.KLDM} AS kldm1,
            #{item.TDDWDM} AS tddwdm1,
            #{item.TDDWMC} AS tddwmc1
            FROM DUAL
        </foreach>
        ) AS t_tddw ON srs_stu_reg.pcdm = t_tddw.pcdm1 AND srs_stu_reg.kldm = t_tddw.kldm1
        AND srs_stu_reg.tddw = t_tddw.tddwdm1
        ) AS temp1
        WHERE temp1.ZY IS NOT NULL
        ORDER BY temp1.ZY, temp1.tdcj DESC;
    </select>

    <select id="selectTDKLDI" resultType="java.util.HashMap">
        SELECT DISTINCT *
        FROM (
        SELECT srs_stu_reg.*, td_kldi.*
        FROM srs_stu_reg
        LEFT JOIN (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.KLDM} AS kldm1,
            #{item.KLMC} AS klmc1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.KLDM = td_kldi.kldm1
        ) AS temp1
        WHERE temp1.ZY IS NOT NULL
        ORDER BY temp1.ZY, temp1.tdcj DESC;
    </select>

    <select id="selectTTDD2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.KSH} AS ksh1,
            #{item.LQZY} AS lqzy1,
            #{item.PCDM} as pcdm1,
            #{item.KLDM} as kldm1,
            #{item.TDCJ} as tdcj1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.KSH = td_kldi.ksh1
        where td_kldi.lqzy1 is not null
    </select>

    <select id="selectTjhk2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.KSH} AS ksh1,
            #{item.LQZY} AS lqzy1,
            #{item.PCDM} as pcdm1,
            #{item.KLDM} as kldm1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.KSH = td_kldi.ksh1
        where td_kldi.lqzy1 is not null
    </select>

    <select id="selectTzykmx2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.KSH} AS ksh1,
            #{item.ZYDH} AS zydh1,
            #{item.TDDWDM} AS tddwdm1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.KSH = td_kldi.ksh1
        where td_kldi.zydh1 is not null
    </select>

    <select id="selectTDKLDM2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.KLDM} AS ksh1,
            #{item.KLMC} AS lqzy1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.kldm2020 = td_kldi.ksh1
        where srs_stu_reg.KLDM2020 is not null
    </select>

    <select id="selectTTDDW2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.PCDM} AS pcdm1,
            #{item.KLDM} AS kldm1,
            #{item.TDDWDM} AS tddwdm1,
            #{item.TDDWMC} AS tddwmc1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.PCDM2020 = td_kldi.pcdm1 and srs_stu_reg.KLDM2020=td_kldi.kldm1
        where srs_stu_reg.KLDM2020 is not null
    </select>

    <select id="selectTdzydh2020" resultType="java.util.Map">
        select DISTINCT srs_stu_reg.*,td_kldi.* from srs_stu_reg
        left join (
        <foreach collection="dataList" item="item" separator="UNION ALL">
            SELECT
            #{item.ZYDH} AS zydh1,
            #{item.KLDM} AS kldm1,
            #{item.ZYMC} AS zymc1
            FROM DUAL
        </foreach>
        ) AS td_kldi ON srs_stu_reg.ZYDH2020 = td_kldi.zydh1
        AND srs_stu_reg.KLDM2020=td_kldi.kldm1
        where srs_stu_reg.ZYDH2020 is not null
    </select>

    <select id="listFdyStu" parameterType="CphSearch" resultType="SrsStuReg">
        select a.*
        from srs_stu_reg as a
        left join srs_class as b on a.BJDM = b.class_code
        left join cph_teacher as c on b.teacher_id = c.teacher_id
        <where>
            <if test="tNo != null  and tNo != ''">and c.employee_id = #{tNo}</if>
        </where>
        order by a.id
    </select>

    <select id="listView" parameterType="CphSearch" resultType="SrsStuReg">
        select a.*,b.class_name,f.major_name,g.dept_name,
        case e.room_no is NULL
        when true then "无"
        else concat(e.park_name,' ',e.building_name,' ',e.room_no) end as dorm
        from srs_stu_reg as a
        left join srs_class as b on a.BJDM = b.class_code
        left join cph_teacher as c on b.teacher_id = c.teacher_id
        left join dms_new_reg as d on a.KSH = d.ksh
        left join view_dorm_info as e on d.dormitory_id = e.id
        left join srs_majors as f on b.major_id = f.major_id
        left join sys_dept as g on f.college_id = g.dept_id
        <where>
            <if test="tNo != null  and tNo != ''">and c.employee_id = #{tNo}</if>
            <if test="XSXM != null  and XSXM != ''">and a.XSXM = #{XSXM}</if>
            <if test="KSH != null  and KSH != ''">and a.KSH = #{KSH}</if>
            <if test="SFZH != null  and SFZH != ''">and a.SFZH = #{SFZH}</if>
            <if test="SJH != null  and SJH != ''">and a.SJH = #{SJH}</if>
            <if test="XB != null  and XB != ''">and a.XB = #{XB}</if>
            <if test="ZZMM != null  and ZZMM != ''">and a.ZZMM = #{ZZMM}</if>
            <if test="MZ != null  and MZ != ''">and a.MZ = #{MZ}</if>
            <if test="XH != null  and XH != ''">and a.XH = #{XH}</if>
            <if test="XY != null  and XY != ''">and a.XY = #{XY}</if>
            <if test="XYDM != null  and XYDM != ''">and a.XYDM = #{XYDM}</if>
            <if test="ZY != null  and ZY != ''">and a.ZY = #{ZY}</if>
            <if test="ZYDM != null  and ZYDM != ''">and a.ZYDM = #{ZYDM}</if>
            <if test="BJ != null  and BJ != ''">and a.BJ = #{BJ}</if>
            <if test="BJDM != null  and BJDM != ''">and a.BJDM = #{BJDM}</if>
            <if test="lastSchool != null  and lastSchool != ''">and a.last_school = #{lastSchool}</if>
            <if test="lastSchoolAddr != null  and lastSchoolAddr != ''">and a.last_school_addr = #{lastSchoolAddr}</if>
            <if test="famName != null  and famName != ''">and a.fam_name like concat('%', #{famName}, '%')</if>
            <if test="famRelation != null  and famRelation != ''">and a.fam_relation = #{famRelation}</if>
            <if test="famPhone != null  and famPhone != ''">and a.fam_phone = #{famPhone}</if>
            <if test="famNowAddr != null  and famNowAddr != ''">and a.fam_now_addr = #{famNowAddr}</if>
            <if test="sfzAddr != null  and sfzAddr != ''">and a.sfz_addr = #{sfzAddr}</if>
            <if test="regStatus != null  and regStatus != ''">and a.reg_status = #{regStatus}</if>
            <if test="delFlag != null  and delFlag != ''">and a.del_flag= #{delFlag}</if>
            <if test="isChangePwd != null  and isChangePwd != ''">and a.is_change_pwd= #{isChangePwd}</if>
        </where>
        order by a.id
    </select>


    <select id="countGender" resultType="DataScreen">
        select count(ksh) as `value`, a.XB as `name`
        from srs_stu_reg as a
        GROUP BY a.XB
    </select>

    <select id="countNowAt" resultType="Map">
        SELECT
            ( SELECT count( ksh ) FROM srs_stu_reg ) AS need_at,
            ( SELECT count( ksh ) FROM srs_stu_reg AS a WHERE a.reg_status = '1' ) AS now_at ,
            ( SELECT count( ksh ) FROM srs_stu_reg AS a WHERE a.reg_status = '2' ) AS now_jh
    </select>

    <select id="countArea" resultType="DataScreen">
        select count(ksh) as `value`, a.SYD as `name`
        from srs_stu_reg as a
        GROUP BY a.SYD
    </select>

    <select id="countDept" resultType="Map">
        select dept_name, IFNULL(num, 0) as num, dept.dept_code
        from sys_dept dept
                 left join (select count(*) as num, sd.dept_code
                            from srs_stu_reg ssr
                                     left join sys_dept sd on ssr.xydm = sd.dept_code
                            where ssr.reg_status = 3
                              and sd.parent_id = 1014
                              and dept_name like "%学院%"
                            group by sd.dept_code) tt on dept.dept_code = tt.dept_code
        where dept.parent_id = 1014
          and dept.dept_name like "%学院%"
          and dept.dept_name not like "%党委办公室%"
    </select>


    <select id="countMajorTop10" resultType="DataScreen">
        select t1.num as `value`, b.major_name as `name`
        from (select count(a.ksh) as num, b.major_code
              from srs_stu_reg as a
                       left join srs_majors as b on a.ZYDM = b.major_code
              where a.reg_status = '3'
              GROUP BY b.major_code) as t1
                 left join srs_majors as b on t1.major_code = b.major_code
        ORDER BY t1.num desc
        limit 0,10
    </select>

    <select id="listCanStu" parameterType="SrsStuReg" resultType="SrsStuReg">
        select a.*,
        concat( c.park_name,' ',c.building_name,' ',c.room_no ) as dorm
        from srs_stu_reg as a
        left join dms_new_reg as b on a.KSH = b.ksh
        left join view_dorm_info as c on b.dormitory_id = c.id
        left join srs_class cl on a.bjdm=class_code
        left join cph_teacher t on cl.teacher_id=t.teacher_id
        <where>
            a.reg_status in (1,3,4)
            <if test="BJDM != null">and a.BJDM = #{BJDM}</if>
            <if test="params.tNo != null">and t.employee_id = #{params.tNo}</if>
            <if test="MZ != null">and a.MZ like concat('%', #{MZ},'%')</if>
            <if test="SYD != null">and a.SYD like concat('%', #{SYD},'%')</if>
            <if test="sfzAddr != null">and a.sfz_addr like concat('%', #{sfzAddr},'%')</if>
        </where>

        order by a.id asc
    </select>

    <select id="countCityStu" resultType="Map">
        select *
        from (
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, SUBSTRING_INDEX(CONCAT(SUBSTRING_INDEX(a.sfz_addr,'市',1),'市'),'广西壮族自治区',-1) as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like "广西壮族自治区%市%"
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, SUBSTRING_INDEX(a.sfz_addr,'省',1) as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%省%' and  a.sfz_addr not like "%自治区%"
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, SUBSTRING_INDEX(a.sfz_addr,'自治区',1) as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%自治区%' and  a.sfz_addr not like "%广西壮族自治区%"
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, '重庆' as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%重庆市%'
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, '北京' as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%北京市%'
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, '上海' as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%上海市%'
                             ) as t1
                         group by t1.city
                     ) as t2
                 union
                 select t2.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.sfz_addr like concat('%',t2.city,'%') ) as baodao
                 from
                     (
                         select t1.city ,count(1) as luqu
                         from(
                                 SELECT a.*, '天津' as 'city'
                                 from srs_stu_reg as a
                                 where a.sfz_addr like '%天津市%'
                             ) as t1
                         group by t1.city
                     ) as t2
             ) as r1
        order by r1.luqu desc

    </select>

    <select id="countDeptBaodao" resultType="Map">
        select t1.*,(select count(1) from srs_stu_reg as a left join srs_stu_check as b on a.KSH = b.ksh where b.is_check = 1 and a.XY = t1.XY) as baodao
        from
            (
                SELECT count(1) as luqu,a.XY
                from srs_stu_reg as a
                group by a.XY
            ) as t1
        order by luqu desc
    </select>

    <select id="listNewBaodao" resultType="Map">
        select a.check_time as baodao_time,b.XY,b.XSXM
        from srs_stu_check as a
        left join srs_stu_reg as b on a.ksh = b.KSH
        where a.is_check = 1
        order by a.check_time desc limit 7
    </select>

    <select id="countStuXb" resultType="Map">
        select a.XY,
               ( select t1.num from (select a.XY,a.XB,count(1) as num
                                     from srs_stu_reg as a
                                     GROUP BY a.XY,a.XB) as t1 where t1.XY = a.XY and t1.XB = '男' ) as boy,
               ( select t1.num from (select a.XY,a.XB,count(1) as num
                                     from srs_stu_reg as a
                                     GROUP BY a.XY,a.XB) as t1 where t1.XY = a.XY and t1.XB = '女' ) as girl
        from srs_stu_reg as a
        GROUP BY a.XY
    </select>

    <select id="countRoomBed" resultType="Map">
        select sum(t1.occupancy) as occupancy,sum(t1.bed_num) as bed_num,t1.park_name,t1.campus_name
        from
            (
                select a.*,b.room_no,b.building_name,b.park_name,b.bed_num,b.campus_name
                from view_dorm_stu_count as a
                         left join view_dorm_info as b on a.dormitory_id = b.id
            ) as t1
        group by t1.campus_name,t1.park_name
        order by t1.campus_name,t1.park_name
    </select>

    <select id="countBaodao" resultType="DataScreen">
        SELECT COUNT(CASE WHEN is_check = 1 THEN 1 END) AS 'value','baodao' as `name`
        FROM srs_stu_check
        union
        select count(1) as `value`,'weijihuo' as `name`
        from srs_stu_reg as a
        where a.reg_status = 0
        union
        select count(1) as `value`,'yijihuodaijiaofei' as `name`
        from srs_stu_reg as a
        where a.reg_status = 2
        union
        select count(1) as `value`,'yijiaofei' as `name`
        from srs_stu_reg as a
        where a.reg_status in (1,3,4)
    </select>

    <update id="updateBathSrsStuRegs">
            UPDATE srs_stu_reg
            SET
            XSXM = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.XSXM}
            </foreach>
            END,
            KSH = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.KSH}
            </foreach>
            END,
            SFZH = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.SFZH}
            </foreach>
            END,
            SJH = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.SJH}
            </foreach>
            END,
            XB = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.XB}
            </foreach>
            END,
            ZZMM = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.ZZMM}
            </foreach>
            END,
            MZ = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.MZ}
            </foreach>
            END,
            XY = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.XY}
            </foreach>
            END,
            ZY = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.ZY}
            </foreach>
            END,
            last_school = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.lastSchool}
            </foreach>
            END,
            fam_now_addr = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.famNowAddr}
            </foreach>
            END,
            sfz_addr = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.sfzAddr}
            </foreach>
            END,
            BJ = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.BJ}
            </foreach>
            END,
            BJDM = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.BJDM}
            </foreach>
            END,
            update_time = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.updateTime}
            </foreach>
            END,
            update_by = CASE id
            <foreach collection="list" item="item">
                WHEN #{item.id} THEN #{item.updateBy}
            </foreach>
            END
            WHERE id IN
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item.id}
            </foreach>
    </update>
    <insert id="insertBathStuRegs">
        insert into srs_stu_reg(XSXM,KSH,SFZH,SJH,XB,ZZMM,MZ,XY,ZY,last_school,fam_now_addr,sfz_addr,create_time,create_by,BJ,BJDM)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.XSXM}, #{item.KSH}, #{item.SFZH}, #{item.SJH},#{item.XB},#{item.ZZMM},#{item.MZ},#{item.XY},#{item.ZY},#{item.lastSchool},#{item.famNowAddr},#{item.sfzAddr},#{item.createTime},#{item.createBy},#{item.BJ},#{item.BJDM})
        </foreach>
    </insert>
</mapper>
