<?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.ym.mec.biz.dal.dao.StudentManageDao">

    <resultMap id="studentManageListDto" type="com.ym.mec.biz.dal.dto.StudentManageListDto">
        <result property="userId" column="user_id_"/>
        <result property="realName" column="real_name_"/>
        <result property="parentsPhone" column="parents_phone_"/>
        <result property="gender" column="gender_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result property="parentsName" column="parents_name_"/>
        <result property="birthdate" column="birthdate_"/>
        <result property="currentGrade" column="current_grade_"/>
        <result property="currentClass" column="current_class_"/>
        <!--<collection property="musicGroups" resultMap="musicGroup" javaType="list"/>-->
    </resultMap>

    <resultMap id="studentManageCourseListDto" type="com.ym.mec.biz.dal.dto.StudentManageCourseListDto">
        <result property="classGroupId" column="class_group_id_" />
        <result property="classGroupName" column="class_group_name_" />
        <result property="classGroupType" column="class_group_type_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result property="courseDate" column="course_date_" />
        <result property="courseStatus" column="course_status_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result property="teacherName" column="teacher_name_" />
        <result property="currentCLassTimes" column="current_class_times_" />
        <result property="attendanceStatus" column="status_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
    </resultMap>

    <resultMap id="studentManageAccountBaseInfo" type="com.ym.mec.biz.dal.dto.StudentManageAccountBaseInfoDto">
        <result property="balance" column="balance_"/>
        <result property="bankName" column="bank_name_"/>
        <result property="cardNo" column="card_no_"/>
    </resultMap>

    <sql id="queryCondition">
        <where>
            cgsm.user_id_ IS NOT NULL
            <if test="organID != null">
              AND s.organ_id_=#{organID}
            </if>
            <if test="musicGroupId != null">
                and mg.id_ = #{musicGroupId}
            </if>
            <if test="studentStatus != null">
                and cgsm.status_ = #{studentStatus, typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
            </if>
            <if test="queryString != null">
                and (su.username_ like CONCAT("%",#{queryString},"%")
                    or su.phone_ like CONCAT("%",#{queryString},"%")
                  )
            </if>
        </where>
    </sql>

    <select id="findStudentsByOrganId" resultMap="studentManageListDto">
        SELECT sr.user_id_,su.real_name_,su.gender_,sr.parents_name_,sr.parents_phone_
        FROM student_registration sr
        LEFT JOIN sys_user su ON sr.user_id_ = su.id_
        WHERE sr.user_id_ IN (
          (SELECT MAX(user_id_) user_id_ FROM student_registration
            <where>
                <if test="search != null and search != ''">
                    parents_name_ LIKE CONCAT('%',#{search},'%') OR parents_phone_ LIKE CONCAT('%',#{search},'%')
                </if>
            </where>
           GROUP BY parents_phone_))
        <if test="organId != null">
            AND su.organ_id_=#{organId}
        </if>
        <include refid="global.limit"/>
    </select>

    <select id="countStudentByOrganId" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT sr.user_id_)
        FROM student_registration sr
        LEFT JOIN sys_user su ON sr.user_id_ = su.id_
        WHERE sr.user_id_ IN (
        (SELECT MAX(user_id_) user_id_ FROM student_registration
        <where>
            <if test="search != null and search != ''">
                parents_name_ LIKE CONCAT('%',#{search},'%') OR parents_phone_ LIKE CONCAT('%',#{search},'%')
            </if>
        </where>
        GROUP BY parents_phone_))
        <if test="organId != null">
            AND su.organ_id_=#{organId}
        </if>
    </select>
    <select id="findStudentBaseInfoByUserID" resultMap="studentManageListDto">
        SELECT su.real_name_,su.gender_,su.birthdate_,sr.parents_name_,su.id_ user_id_,
        sr.parents_phone_,sr.current_class_,sr.current_grade_
        FROM sys_user su
        LEFT JOIN student_registration sr ON su.id_ = sr.user_id_
        WHERE su.id_ =#{userId} AND su.user_type_ LIKE CONCAT('%',"STUDENT",'%')
    </select>

    <select id="findGoodsNamesByIds" resultType="string">
      SELECT name_ FROM goods WHERE id_ IN
      <foreach collection="ids" item="id" open="(" close=")" separator=",">
          #{id}
      </foreach>
    </select>

    <resultMap id="musicGroup" type="com.ym.mec.biz.dal.dto.StudentManageBaseInfoOfMusicGroupDto">
        <result property="musicGroupId" column="music_group_id_"/>
        <result property="musicGroupName" column="music_group_name_"/>
        <result property="musicGroupStatus" column="music_group_status_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result property="joinMusicGroupDate" column="join_date_"/>
        <result property="subjectId" column="subject_id_"/>
        <result property="subjectName" column="subject_name_"/>
        <result property="kitPurchaseMethod" column="kit_purchase_method_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result property="depositFee" column="deposit_fee_"/>
        <result property="userId" column="user_id_"/>
        <result property="userMusicGroupStatus" column="user_music_group_status_" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
    </resultMap>
    <select id="findStudentMusicGroupsByUserId" resultMap="musicGroup">
        SELECT mg.id_ music_group_id_,sr.create_time_ join_date_,mg.name_ music_group_name_,mg.status_ music_group_status_,
        s.id_ subject_id_,s.name_ subject_name_,sr.kit_purchase_method_,mgsp.deposit_fee_,sr.user_id_,sr.music_group_status_ user_music_group_status_
        FROM student_registration sr
        LEFT JOIN music_group mg ON sr.music_group_id_ = mg.id_
        LEFT JOIN `subject` s ON s.id_ = sr.actual_subject_id_
        LEFT JOIN music_group_subject_plan mgsp ON mg.id_ = mgsp.music_group_id_ AND s.id_ = mgsp.subject_id_
        WHERE sr.user_id_=#{userId}
    </select>

    <sql id="studentManageCourseQueryCondition">
        <where>
            <if test="studentId!=null">
                and cgsm.user_id_=#{studentId}
            </if>
            <if test="musicGroupId!=null">
                and cg.music_group_id_=#{musicGroupId}
            </if>
            <if test="classGroupType!=null">
                and cg.type_=#{classGroupType}
            </if>
            <if test="courseStatus!=null">
                and cs.status_=#{courseStatus}
            </if>
            <if test="classGroupName!=null">
                and cg.name_ like CONCAT("%",#{classGroupName},"%")
            </if>
            <if test="teacherName!=null">
                and su.username_ like CONCAT("%",#{teacherName},"%")
            </if>
            <if test="vipGroupId!=null">
                and vgcgm.vip_group_id_=#{vipGroupId}
            </if>
        </where>
    </sql>

    <select id="findStudentCourseList" resultMap="studentManageCourseListDto">
        SELECT
            cg.id_ class_group_id_,
            cg.name_ class_group_name_,
            cg.type_ class_group_type_,
            CONCAT(cs.class_date_," ",cs.start_class_time_) course_date_,
            cs.status_ course_status_,
            su.username_ teacher_name_
        FROM class_group cg
            LEFT JOIN class_group_student_mapper cgsm ON cg.id_=cgsm.class_group_id_
            <if test="vipGroupId!=null">
                LEFT JOIN vip_group_class_group_mapper vgcgm ON cgsm.class_group_id_=vgcgm.class_group_id_
            </if>
            LEFT JOIN course_schedule cs ON cg.id_=cs.class_group_id_
            LEFT JOIN sys_user su ON cs.teacher_id_=su.id_
        <include refid="studentManageCourseQueryCondition"/>
        ORDER BY course_date_
        <include refid="global.limit"/>
    </select>

    <select id="countStudentCourses" resultType="int">
        SELECT count(cg.*) FROM class_group cg
        LEFT JOIN class_group_student_mapper cgsm ON cg.id_=cgsm.class_group_id_
        <if test="vipGroupId!=null">
            LEFT JOIN vip_group_class_group_mapper vgcgm ON cgsm.class_group_id_=vgcgm.class_group_id_
        </if>
        LEFT JOIN course_schedule cs ON cg.id_=cs.class_group_id_
        LEFT JOIN sys_user su ON cs.teacher_id_=su.id_
        <include refid="studentManageCourseQueryCondition"/>
    </select>

    <sql id="studentManageAttendanceQueryCondition">
        <where>
            <if test="studentId!=null">
                and sa.user_id_=#{studentId}
            </if>
            <if test="classGroupType!=null">
                and cg.type_=#{classGroupType}
            </if>
            <if test="attendanceStatus!=null">
                and sa.status_=#{attendanceStatus, typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
            </if>
            <if test="classGroupName!=null">
                and cg.name_ like CONCAT("%",#{classGroupName},"%")
            </if>
            <if test="teacherName!=null">
                and su.real_name_ like CONCAT("%",#{teacherName},"%")
            </if>
        </where>
    </sql>

    <select id="findStudentAttendances" resultMap="studentManageCourseListDto">
        SELECT
            cg.id_ class_group_id_,
            cg.name_ class_group_name_,
            cg.type_ class_group_type_,
            CONCAT(cs.class_date_," ",cs.start_class_time_) course_date_,
            su.real_name_ teacher_name_,
            sa.status_,
            sa.current_class_times_
        FROM
            student_attendance sa
            LEFT JOIN class_group cg ON sa.class_group_id_=cg.id_
            LEFT JOIN course_schedule cs ON sa.course_schedule_id_=cs.id_
            LEFT JOIN sys_user su ON sa.teacher_id_=su.id_
        <include refid="studentManageAttendanceQueryCondition"/>
        ORDER BY sa.id_
        <include refid="global.limit"/>
    </select>

    <select id="countStudentAttendances" resultType="int">
        SELECT
        count(*)
        FROM
        student_attendance sa
        LEFT JOIN class_group cg ON sa.class_group_id_=cg.id_
        LEFT JOIN course_schedule cs ON sa.course_schedule_id_=cs.id_
        LEFT JOIN sys_user su ON sa.teacher_id_=su.id_
        <include refid="studentManageAttendanceQueryCondition"/>
    </select>

    <select id="getStudentAccountBaseInfo" resultMap="studentManageAccountBaseInfo">
        SELECT
            suca.balance_,
            subc.bank_name_,
            subc.card_no_
        FROM
            sys_user_cash_account suca
            LEFT JOIN sys_user_bank_card subc ON suca.user_id_ = subc.user_id_
            AND subc.is_default_ = 1
        WHERE
            suca.user_id_ =#{userId}
    </select>

</mapper>