<?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.xinyilu.system.mapper.classinfo.ClassInfoMapper">

    <resultMap type="classInfoVO" id="ClassInfoResult">
        <result property="id" column="id"/>
        <result property="kindergartenId" column="kindergarten_id"/>
        <result property="className" column="class_name"/>
        <result property="classType" column="class_type"/>
        <result property="classTeacher" column="class_teacher"/>
        <result property="phone" column="phone"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="kindergartenName" column="kindergarten_name"/>
        <result property="userId" column="user_id"/>
        <result property="entranceDate" column="entrance_date"/>
    </resultMap>

    <sql id="selectClassInfoVo">
        select id,
               kindergarten_id,
               class_name,
               class_type,
               class_teacher,
               phone,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time,
               entrance_date,
               user_id
        from class_info
    </sql>

    <select id="selectClassInfoList" parameterType="classInfoDTO" resultMap="ClassInfoResult">
        SELECT
        ci.id,
        ci.kindergarten_id,
        ki.kindergarten_name,
        ci.class_name,
        ci.class_type,
        ci.class_teacher,
        ci.phone,
        ci.del_flag,
        ci.create_by,
        ci.create_time,
        ci.update_by,
        ci.update_time,
        YEAR(ci.entrance_date) entranceYear,
        ci.user_id
        FROM
        class_info ci
        LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="className != null  and className != ''">
                and ci.class_name like concat('%', #{className}, '%')
            </if>
            <if test="classType != null  and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="classTeacher != null  and classTeacher != ''">
                and ci.class_teacher = #{classTeacher}
            </if>
            <if test="phone != null  and phone != ''">
                and ci.phone = #{phone}
            </if>
            <!-- 查询区域角色的班级信息 -->
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ci.kindergarten_id in
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <!-- 查询角色为老师和家长所在班级信息 -->
            <if test="classIdList != null and classIdList.size() > 0">
                and ci.id in
                <foreach collection="classIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            and ci.del_flag = 0
        </where>
        order by ci.create_time desc
    </select>

    <select id="selectClassInfoCount" parameterType="classInfoDTO" resultType="int">
        SELECT
        count(1)
        FROM
        class_info ci
        LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="className != null  and className != ''">
                and ci.class_name like concat('%', #{className}, '%')
            </if>
            <if test="classType != null  and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="classTeacher != null  and classTeacher != ''">
                and ci.class_teacher = #{classTeacher}
            </if>
            <if test="phone != null  and phone != ''">
                and ci.phone = #{phone}
            </if>
            <!-- 查询区域角色的班级信息 -->
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ci.kindergarten_id in
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <!-- 查询角色为老师和家长所在班级信息 -->
            <if test="classIdList != null and classIdList.size() > 0">
                and ci.id in
                <foreach collection="classIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            and ci.del_flag = 0
        </where>
    </select>

    <select id="selectClassInfoById" parameterType="Long"
            resultMap="ClassInfoResult">
        SELECT ci.id,
               ci.kindergarten_id,
               ki.kindergarten_name,
               ci.class_name,
               ci.class_type,
               ci.class_teacher,
               ci.phone,
               ci.del_flag,
               ci.create_by,
               ci.create_time,
               ci.update_by,
               ci.update_time,
               ci.entrance_date as entranceDateStr,
               ci.user_id
        FROM class_info ci
                 LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        where ci.id = #{id}
          and ci.del_flag = 0
    </select>

    <insert id="insertClassInfo" parameterType="ClassInfoDTO" useGeneratedKeys="true"
            keyProperty="id">
        insert into class_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id,
            </if>
            <if test="className != null">class_name,
            </if>
            <if test="classType != null">class_type,
            </if>
            <if test="classTeacher != null">class_teacher,
            </if>
            <if test="phone != null">phone,
            </if>
            <if test="delFlag != null">del_flag,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
            <if test="userId != null">user_id,
            </if>
            <if test="entranceDate != null">entrance_date,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">#{kindergartenId},
            </if>
            <if test="className != null">#{className},
            </if>
            <if test="classType != null">#{classType},
            </if>
            <if test="classTeacher != null">#{classTeacher},
            </if>
            <if test="phone != null">#{phone},
            </if>
            <if test="delFlag != null">#{delFlag},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
            <if test="userId != null">#{userId},
            </if>
            <if test="entranceDate != null">#{entranceDate},
            </if>
        </trim>
    </insert>

    <update id="updateClassInfo" parameterType="ClassInfoDTO">
        update class_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id =
                #{kindergartenId},
            </if>
            <if test="className != null">class_name =
                #{className},
            </if>
            <if test="classType != null">class_type =
                #{classType},
            </if>
            <if test="classTeacher != null">class_teacher =
                #{classTeacher},
            </if>
            <if test="phone != null">phone =
                #{phone},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
            <if test="entranceDate != null">entrance_date =
                #{entranceDate},
            </if>
            <if test="userId != null">user_id =
                #{userId},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteClassInfoById" parameterType="Long">
        delete
        from class_info
        where id = #{id}
    </delete>

    <delete id="deleteClassInfoByIds" parameterType="String">
        delete from class_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <update id="updateClassInfoById" parameterType="String">
        update class_info set del_flag = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="getKindergartenInfoByAreaId" parameterType="long" resultType="long">
        SELECT ki.id
        FROM kindergarten_info ki
        LEFT JOIN organize_info oi ON ki.organize_id = oi.id
        LEFT JOIN sys_area sa ON oi.area_id = sa.id
        <where>
            and ki.del_flag = 0
            <if test="level != null">
                and sa.level = #{level}
            </if>
            <if test="sysAreaIdList != null and sysAreaIdList.size() > 0">
                and sa.id in
                <foreach collection="sysAreaIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getKindergartenIdByPrincipalId" parameterType="long" resultType="long">
        select id
        from kindergarten_info
        where principal_id = #{principalId}
          and del_flag = 0
    </select>

    <select id="getKindergartenNameByPrincipalId"
            resultType="com.xinyilu.system.domain.kindergarteninfo.KindergartenInfo">
        select id, kindergarten_name
        from kindergarten_info
        where principal_id = #{principalId}
          and del_flag = 0
    </select>

    <select id="getClassIdByUserId" parameterType="long" resultType="long">
        SELECT cti.class_id
        FROM class_teacher_info cti
                 INNER JOIN teacher_info ti ON cti.teacher_id = ti.id
        where ti.user_id = #{userId}
          and ti.del_flag = 0
    </select>

    <select id="getClassIdByParentId" parameterType="String" resultType="long">
        SELECT si.class_id
        FROM parent_info pi
                 LEFT JOIN parent_student_info ps ON pi.id = ps.parent_id
                 LEFT JOIN student_info si ON ps.student_id = si.id
        where pi.phone = #{phone}
          and pi.del_flag = 0
        GROUP BY si.class_id
    </select>

    <select id="getClassNameNumberInfo" resultType="integer">
        SELECT
        count( 1 )
        FROM
        class_info
        <where>
            <if test="kindergartenId != null ">
                and kindergarten_id = #{kindergartenId}
            </if>
            <if test="className != null  and className != ''">
                and class_name = #{className}
            </if>
            <if test="classType != null  and classType != ''">
                and class_type = #{classType}
            </if>
            and del_flag = 0
        </where>
    </select>

    <select id="getClassPhoneCountInfo" resultType="int">
        SELECT
        count( 1 )
        FROM
        class_info
        <where>
            <if test="phone != null  and phone != ''">
                and phone = #{phone}
            </if>
            and del_flag = 0
        </where>
    </select>

    <update id="updateClassTypes" parameterType="ClassTypeDTO">
        update class_info
        set class_type  = #{nowClassType},
            update_by   = #{updateBy},
            update_time = #{updateTime}
        where kindergarten_id = #{kindergartenId}
          and class_type = #{originClassType}
    </update>

    <select id="getClassList" resultType="com.xinyilu.system.domain.classinfo.ClassInfo">
        select id,
        class_name,
        class_type
        from class_info
        <where>
            del_flag = 0
            <if test="kindergartenId != null ">
                and kindergarten_id = #{kindergartenId}
            </if>
            <if test="className != null  and className != ''">
                and class_name like concat('%', #{className}, '%')
            </if>
        </where>
    </select>

    <select id="getInfoByClassId" resultType="com.xinyilu.system.domain.vo.scoreExport.ScoreExportVO">
        SELECT
        ki.kindergarten_name,
        ci.class_name,
        COUNT(DISTINCT(ts.student_id)) as studentCount
        FROM
        class_info ci
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        INNER JOIN student_info si ON ci.id=si.class_id
        INNER JOIN test_item_score ts ON si.id = ts.student_id
        <where>
            ci.del_flag = 0  and ts.del_flag=0
            <if test="classId != null ">
                and ci.id = #{classId}
            </if>
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="studentSex != null  and studentSex != ''">
                and si.student_sex=#{studentSex}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND ts.batch_code = #{batchCode}
            </if>
        </where>
    </select>

    <select id="getBirthDateClassId" resultType="date">
        SELECT
        si.birth_date
        FROM
        class_info ci
        INNER JOIN student_info si ON ci.id=si.class_id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            ci.del_flag = 0 and si.del_flag=0
            <if test="classId != null ">
                and ci.id = #{classId}
            </if>
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="studentSex != null  and studentSex != ''">
                and si.student_sex=#{studentSex}
            </if>
        </where>
    </select>
</mapper>
