<?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.qmth.teachcloud.common.mapper.BasicCourseMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.teachcloud.common.entity.BasicCourse">
        <result column="id" property="id"/>
        <result column="school_id" property="schoolId"/>
        <result column="code" property="code"/>
        <result column="name" property="name"/>
        <result column="create_id" property="createId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_id" property="updateId"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        select id,
               school_id,
               code,
               name,
               create_id,
               create_time,
               update_id,
               update_time
        from basic_course
    </sql>

    <select id="listCoursesByUserId" resultMap="BaseResultMap">
        SELECT a.id,
               a.code,
               a.name
        FROM basic_course a
                 JOIN
             basic_user_course b ON a.id = b.course_id
        WHERE b.user_id = #{id}
    </select>

    <select id="listPage" resultMap="BaseResultMap">
        <include refid="Base_Column_List"></include>
        <where>
            <if test="schoolId != null and schoolId != ''">
                and school_id = #{schoolId}
            </if>
            <if test="code != null and code != ''">
                and code like concat('%',#{code} ,'%')
            </if>
            <if test="name != null and name != ''">
                and name like concat('%',#{name} ,'%')
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="findByUserLoginNameAndRealName"
            resultType="com.qmth.teachcloud.common.bean.dto.CourseInfoDto">
        SELECT bc.code AS courseCode,
               bc.name AS courseName
        FROM basic_course bc
                 INNER JOIN
             basic_user_course buc ON buc.course_id = bc.id
                 INNER JOIN
             sys_user su ON su.id = buc.user_id
        WHERE su.login_name = #{loginName}
          AND real_name = #{realName};
    </select>

    <select id="findBasicCoursePage" resultType="com.qmth.teachcloud.common.bean.result.BasicCourseResult">
        SELECT
        bc.id,
        bc.code AS courseCode,
        bc.name AS courseName,
        teaching_room_id AS teachingRoomId,
        org.name AS teachingRoomName,
        bc.enable AS enable,
        bc.create_id AS createId,
        bc.create_time AS createTime
        FROM
        basic_course bc
        LEFT JOIN
        sys_org org ON bc.teaching_room_id = org.id
        AND org.enable = TRUE
        <where>
            <if test="belongOrgId != null and belongOrgId != ''">
                AND bc.teaching_room_id = #{belongOrgId}
            </if>
            <if test="courseName != null and courseName != ''">
                AND bc.name LIKE CONCAT('%',#{courseName},'%')
            </if>
            <if test="startCreateTime != null and startCreateTime != ''">
                AND bc.create_time >= #{startCreateTime}
            </if>
            <if test="endCreateTime != null and endCreateTime != ''">
                AND #{endCreateTime} >= bc.create_time
            </if>
            <if test="schoolId != null and schoolId != ''">
                and bc.school_id = #{schoolId}
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND bc.create_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND bc.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        ORDER BY createTime DESC
    </select>

    <select id="findExamTaskByCourseCode" resultType="java.util.Map">
        SELECT
        course_code AS courseCode, course_name AS courseName
        FROM
        exam_task
        <where>
            AND school_id = #{schoolId}
            <if test="courseCodeSet != null and courseCodeSet != '' and courseCodeSet.size > 0">
                AND course_code IN
                <foreach collection="courseCodeSet" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY course_code,course_name
    </select>
    <select id="listExportData" resultType="com.qmth.teachcloud.common.bean.dto.BasicCourseExportDto">
        SELECT
        bc.id,
        bc.code AS courseCode,
        bc.name AS courseName,
        teaching_room_id AS teachingRoomId,
        org.name AS teachingRoomName,
        bc.enable AS enable,
        bc.create_id AS createId,
        bc.create_time AS createTime
        FROM
        basic_course bc
        LEFT JOIN
        sys_org org ON bc.teaching_room_id = org.id
        AND org.enable = TRUE
        <where>
            <if test="belongOrgId != null and belongOrgId != ''">
                AND bc.teaching_room_id = #{belongOrgId}
            </if>
            <if test="courseName != null and courseName != ''">
                AND bc.name LIKE CONCAT('%',#{courseName},'%')
            </if>
            <if test="startCreateTime != null and startCreateTime != ''">
                AND bc.create_time >= #{startCreateTime}
            </if>
            <if test="endCreateTime != null and endCreateTime != ''">
                AND #{endCreateTime} >= bc.create_time
            </if>
            <if test="schoolId != null and schoolId != ''">
                and bc.school_id = #{schoolId}
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND bc.create_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND bc.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        ORDER BY createTime DESC
    </select>
</mapper>
