<?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.koobietech.eas.dao.mapper.AttendanceMapper">
    <resultMap id="camelCaseResultMap" type="com.koobietech.eas.dao.pojo.AttendancePojo">
        <result property="id" column="id"/>
        <result property="checkinDate" column="checkin_date"/>
        <result property="morning" column="morning"/>
        <result property="afternoon" column="afternoon"/>
        <result property="month" column="month"/>
        <result property="studentId" column="student_id"/>
        <result property="type" column="type"/>
        <result property="className" column="class_name"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="categoryName" column="category_name"/>
        <result property="subjectsName" column="subjects_name"/>
        <result property="studentName" column="student_name"/>
        <result property="studentNumber" column="student_number"/>
        <result property="teacherName" column="teacher_name"/>
        <result property="roomName" column="room_name"/>
    </resultMap>
    <select id="queryAttendance" resultMap="camelCaseResultMap">
        SELECT
            eas_edu_schedule.id,
            eas_arc_tls_attendance.checkin_date,
            eas_arc_tls_attendance.morning,
            eas_arc_tls_attendance.afternoon,
            eas_arc_tls_attendance.`month`,
            eas_edu_clt_relation.student_id,
            eas_edu_clt_relation.type,
            eas_edu_class.`name` AS class_name,
            eas_edu_schedule.start_time,
            eas_edu_schedule.end_time,
            eas_edu_category.`name` AS category_name,
            eas_edu_subjects.`name` AS subjects_name,
            eas_sys_student.student_name,
            eas_sys_student.student_number,
            eas_sys_userinfo.relname AS teacher_name,
            eas_edu_classroom.`name` AS room_name
        FROM
            eas_edu_clt_relation
            LEFT JOIN eas_edu_class ON eas_edu_class.id = eas_edu_clt_relation.class_id
            JOIN eas_edu_schedule ON eas_edu_schedule.class_id = eas_edu_class.id
            LEFT JOIN eas_edu_category ON eas_edu_category.id = eas_edu_schedule.category_id
            LEFT JOIN eas_edu_subjects ON eas_edu_subjects.id = eas_edu_schedule.subjects_id
            LEFT JOIN eas_edu_classroom ON eas_edu_classroom.id = eas_edu_schedule.room_id
            LEFT JOIN eas_sys_userinfo ON eas_sys_userinfo.id = eas_edu_schedule.teacher_id
            LEFT JOIN eas_sys_student ON eas_sys_student.id = eas_edu_clt_relation.student_id
            LEFT JOIN eas_arc_tls_attendance ON eas_arc_tls_attendance.schedule_id = eas_edu_schedule.id AND eas_arc_tls_attendance.student_number = eas_sys_student.student_number
        WHERE 1 = 1
        <if test="id != null and id > 0">
            AND eas_edu_schedule.id = #{id}
        </if>
        <if test="studentId != null and studentId > 0">
            AND eas_sys_student.id = #{studentId}
        </if>
        <if test="month != null and month > 0">
            AND eas_arc_tls_attendance.`month` = #{month}
        </if>
        <if test="startTime != null">
            AND eas_edu_schedule.start_time >= #{startTime}
        </if>
        <if test="endTime != null">
            AND eas_edu_schedule.end_time &lt;= #{endTime}
        </if>
        <if test="teacherId != null and teacherId > 0">
            AND eas_sys_userinfo.id = #{teacherId}
        </if>
        <if test="classId != null and classId > 0">
            AND eas_edu_class.id = #{classId}
        </if>
        <if test="studentName != null and studentName != ''">
            AND eas_sys_student.student_name = #{studentName}
        </if>
        <if test="roomName != null and roomName > 0">
            AND eas_edu_classroom.id = #{roomName}
        </if>
        <if test="className != null and className > 0">
            AND eas_edu_class.id = #{className}
        </if>
    </select>

    <select id="queryAttendanceCount" resultType="long" >
        SELECT
            count(*) AS total
        FROM
        eas_edu_clt_relation
        LEFT JOIN eas_edu_class ON eas_edu_class.id = eas_edu_clt_relation.class_id
        LEFT JOIN eas_edu_schedule ON eas_edu_schedule.class_id = eas_edu_class.id
        LEFT JOIN eas_edu_category ON eas_edu_category.id = eas_edu_schedule.category_id
        LEFT JOIN eas_edu_subjects ON eas_edu_subjects.id = eas_edu_schedule.subjects_id
        LEFT JOIN eas_edu_classroom ON eas_edu_classroom.id = eas_edu_schedule.room_id
        LEFT JOIN eas_sys_userinfo ON eas_sys_userinfo.id = eas_edu_schedule.teacher_id
        LEFT JOIN eas_sys_student ON eas_sys_student.id = eas_edu_clt_relation.student_id
        LEFT JOIN eas_arc_tls_attendance ON eas_arc_tls_attendance.schedule_id = eas_edu_schedule.id AND eas_arc_tls_attendance.student_number = eas_sys_student.student_number
        WHERE 1 = 1
        <if test="id != null and id > 0">
            AND eas_edu_schedule.id = #{id}
        </if>
        <if test="studentId != null and studentId > 0">
            AND eas_sys_student.id = #{studentId}
        </if>
        <if test="month != null and month > 0">
            AND eas_arc_tls_attendance.`month` = #{month}
        </if>
        <if test="startTime != null">
            AND eas_edu_schedule.start_time >= #{startTime}
        </if>
        <if test="endTime != null">
            AND eas_edu_schedule.end_time &lt;= #{endTime}
        </if>
        <if test="teacherId != null and teacherId > 0">
            AND eas_sys_userinfo.id = #{teacherId}
        </if>
        <if test="classId != null and classId > 0">
            AND eas_edu_class.id = #{classId}
        </if>
        <if test="studentName != null and studentName != ''">
            AND eas_sys_student.student_name = #{studentName}
        </if>
        <if test="roomName != null and roomName > 0">
            AND eas_edu_classroom.id = #{roomName}
        </if>
        <if test="className != null and className > 0">
            AND eas_edu_class.id = #{className}
        </if>
    </select>

</mapper>