<?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.IndexErrDataRecordDao">

	<resultMap type="com.ym.mec.biz.dal.entity.IndexErrDataRecord" id="IndexErrDataRecord">
		<result column="id_" property="id" />
		<result column="organ_id_" property="organId" />
		<result column="data_id_" property="dataId" />
		<result column="data_type_" property="dataType" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler" />
		<result column="deal_user_id_" property="dealUserId" />
		<result column="generate_time_" property="generateTime"/>
		<result column="create_time_" property="createTime" />
		<result column="deal_time_" property="dealTime" />
		<result column="tenant_id_" property="tenantId" />
	</resultMap>

	<!-- 根据主键查询一条记录 -->
	<select id="get" resultMap="IndexErrDataRecord" >
		SELECT * FROM index_err_data_record WHERE id_ = #{id}
	</select>

	<!-- 全查询 -->
	<select id="findAll" resultMap="IndexErrDataRecord">
		SELECT * FROM index_err_data_record where tenant_id_ = #{tenantId} ORDER BY id_
	</select>

	<!-- 向数据库增加一条记录 -->
	<insert id="insert" parameterType="com.ym.mec.biz.dal.entity.IndexErrDataRecord" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
		<!--
		<selectKey resultClass="int" keyProperty="id" > 
		SELECT SEQ_WSDEFINITION_ID.nextval AS ID FROM DUAL 
		</selectKey>
		-->
		INSERT INTO index_err_data_record (organ_id_,data_id_,data_type_,deal_user_id_,generate_time_,deal_time_,create_time_,tenant_id_)
		VALUES(#{organId},#{dataId},#{dataType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},#{dealUserId},#{generateTime},#{dealTime},NOW(),#{tenantId})
	</insert>

	<insert id="batchInsert" parameterType="com.ym.mec.biz.dal.entity.IndexErrDataRecord" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
		INSERT INTO index_err_data_record (organ_id_,data_id_,data_type_,deal_user_id_,generate_time_,deal_time_,create_time_,tenant_id_)
		VALUE
		<foreach collection="datas" item="data" separator=",">
			(#{data.organId},#{data.dataId},#{data.dataType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},#{data.dealUserId},
			#{data.generateTime},#{data.dealTime},NOW(),#{data.tenantId})
		</foreach>
	</insert>

	<!-- 根据主键查询一条记录 -->
	<update id="update" parameterType="com.ym.mec.biz.dal.entity.IndexErrDataRecord">
		UPDATE index_err_data_record
		<set>
			<if test="organId != null">
				organ_id_ = #{organId},
			</if>
			<if test="dealUserId != null">
				deal_user_id_ = #{dealUserId},
			</if>
			<if test="dataId != null">
				data_id_ = #{dataId},
			</if>
			<if test="dataType != null">
				data_type_ = #{dataType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},
			</if>
			<if test="generateTime != null">
				generate_time_ = #{generateTime},
			</if>
			<if test="dealTime != null">
				deal_time_ = #{dealTime},
			</if>
		</set>
		WHERE id_ = #{id} and tenant_id_ = #{tenantId}
	</update>

	<update id="batchUpdate" parameterType="com.ym.mec.biz.dal.entity.IndexErrDataRecord">
		<foreach collection="datas" item="data" separator=";">
			UPDATE index_err_data_record
			<set>
				<if test="data.organId != null">
					organ_id_ = #{data.organId},
				</if>
				<if test="data.dealUserId != null">
					deal_user_id_ = #{data.dealUserId},
				</if>
				<if test="data.dataId != null">
					data_id_ = #{data.dataId},
				</if>
				<if test="data.dataType != null">
					data_type_ = #{data.dataType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},
				</if>
				<if test="data.generateTime != null">
					generate_time_ = #{data.generateTime},
				</if>
				<if test="data.dealTime != null">
					deal_time_ = #{data.dealTime},
				</if>
			</set>
			WHERE id_ = #{data.id} and tenant_id_ = #{data.tenantId}
		</foreach>
	</update>

	<!-- 根据主键删除一条记录 -->
	<delete id="delete" >
		DELETE FROM index_err_data_record WHERE id_ = #{id}
	</delete>

	<delete id="deleteWithGenerateTimeAndType" >
		DELETE FROM index_err_data_record
		WHERE
			generate_time_ = #{generateTime}
			<if test="dataType!=null">
				AND data_type_=#{dataType, typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
	</delete>

	<!-- 分页查询 -->
	<select id="queryPage" resultMap="IndexErrDataRecord" parameterType="map">
		SELECT * FROM index_err_data_record where tenant_id_ = #{tenantId} ORDER BY id_ <include refid="global.limit"/>
	</select>

	<!-- 查询当前表的总记录数 -->
	<select id="queryCount" resultType="int">
		SELECT COUNT(*) FROM index_err_data_record where tenant_id_ = #{tenantId}
	</select>

	<select id="countWithGenerateTime" resultType="int">
		SELECT COUNT(id_) FROM index_err_data_record WHERE generate_time_=#{generateTime}
	</select>

	<select id="getWithGenerateTime" resultMap="IndexErrDataRecord">
		SELECT * FROM index_err_data_record
		<where>
			tenant_id_ = #{tenantId}
			<if test="organIds!=null and organIds.size()>0">
				AND organ_id_ IN
				<foreach collection="organIds" item="organId" open="(" close=")" separator=",">
					#{organId}
				</foreach>
			</if>
			<if test="generateStartTime!=null and generateStartTime!=''">
				AND generate_time_&gt;=#{generateStartTime}
			</if>
			<if test="generateEndTime!=null and generateEndTime!=''">
				AND generate_time_&lt;=#{generateEndTime}
			</if>
		</where>
	</select>

	<select id="findUntreatedRecord" resultMap="IndexErrDataRecord">
		SELECT * FROM index_err_data_record WHERE deal_time_ IS NULL
	</select>

	<select id="queryErrInspection" resultMap="IndexErrDataRecord">
		SELECT
			id_ data_id_,
		    organ_id_,
		    user_id_ deal_user_id_,tenant_id_
		FROM
			inspection_item_plan
		WHERE
			conclusion_status_ = 1 AND memo_ = ''
			<if test="date!=null">
				AND DATE_FORMAT(plan_start_,'%Y-%m-%d') = #{date}
			</if>
			<if test="dates!=null and dates.size()>0">
				AND DATE_FORMAT(plan_start_,'%Y-%m-%d') IN
				<foreach collection="dates" item="dt" separator="," open="(" close=")">
					#{dt}
				</foreach>
			</if>
	</select>

	<select id="queryInspectionItemPlan" resultMap="IndexErrDataRecord">
		SELECT
			iip.id_ data_id_,
			iip.organ_id_,
			iip.user_id_ deal_user_id_,iip.tenant_id_
		FROM inspection_item_plan iip
		WHERE iip.status_ = 0 AND iip.memo_ = ''
			AND DATE_FORMAT(iip.plan_start_,'%Y-%m-%d') &lt; DATE_FORMAT(NOW(),'%Y-%m-%d')
			AND DATE_FORMAT(iip.plan_start_,'%Y-%m-%d') &lt;= DATE_FORMAT(NOW(),'%Y-%m-%d')
			<if test="date!=null">
		  		AND DATE_FORMAT(iip.plan_start_,'%Y-%m-%d') = #{date}
			</if>
			<if test="dates!=null and dates.size()>0">
				AND DATE_FORMAT(iip.plan_start_,'%Y-%m-%d') IN
				<foreach collection="dates" item="dt" separator="," open="(" close=")">
					#{dt}
				</foreach>
			</if>
	</select>

	<select id="queryStudentVisit" resultMap="IndexErrDataRecord">
		SELECT
			ii.id_ data_id_,
		    ii.organ_id_,
		    ii.user_id_ deal_user_id_
		FROM inspection_item ii
		LEFT JOIN inspection i ON ii.inspection_id_ = i.id_
		WHERE ii.item_ = 'VISIT' AND ii.memo_ =''
			AND ii.times_ > (SELECT COUNT(DISTINCT sv.id_) FROM student_visit sv
			WHERE sv.teacher_id_ = ii.user_id_ AND DATE_FORMAT(i.month_,'%Y-%m') = DATE_FORMAT(sv.visit_time_,'%Y-%m'))
			AND i.month_ &lt;= DATE_FORMAT(NOW(),'%Y-%m-%d')
			<if test="date!=null">
				AND i.month_ = #{date}
			</if>
			<if test="dates!=null and dates.size()>0">
				AND i.month_ IN
				<foreach collection="dates" item="dt" separator="," open="(" close=")">
					#{dt}
				</foreach>
			</if>
	</select>

	<select id="getAttendanceError" resultMap="IndexErrDataRecord">
		SELECT
			DISTINCT cs.id_ data_id_,
			cs.tenant_id_,
			cs.organ_id_,
			CASE cs.group_type_ WHEN 'MUSIC' THEN mg.educational_teacher_id_ WHEN 'VIP' THEN vg.educational_teacher_id_ WHEN 'PRACTICE' THEN pg.educational_teacher_id_ END deal_user_id_
		FROM course_schedule cs
			LEFT JOIN teacher_attendance ta ON ta.course_schedule_id_ = cs.id_
			LEFT JOIN course_schedule_student_payment cssp ON cssp.course_schedule_id_ = cs.id_
			LEFT JOIN student_attendance sa ON sa.course_schedule_id_ = cssp.course_schedule_id_ AND cssp.user_id_ = sa.user_id_
			LEFT JOIN music_group mg ON cs.music_group_id_=mg.id_ AND cs.group_type_='MUSIC'
			LEFT JOIN vip_group vg ON cs.music_group_id_=vg.id_ AND cs.group_type_='VIP'
			LEFT JOIN practice_group pg ON cs.music_group_id_=pg.id_ AND cs.group_type_='PRACTICE'
		WHERE ta.teacher_id_ = cs.actual_teacher_id_
			AND cs.status_ = 'OVER' AND cs.del_flag_ = 0 AND cs.class_date_ >= '2021-02-01'
			AND (((ta.sign_in_status_ = 0 OR ta.sign_in_status_ IS NULL OR ta.sign_out_status_ = 0 OR ta.sign_out_status_ IS NULL) AND ta.dispose_content_ IS NULL) OR (sa.id_ IS NULL OR (sa.status_ = 'TRUANT' AND sa.visit_flag_ = 0)))
			AND ((ta.sign_in_status_ IS NULL AND ta.sign_out_status_ IS NOT NULL) OR (ta.sign_out_status_ IS NULL AND ta.sign_in_status_ IS NOT NULL) OR (ta.sign_out_status_ IS NOT NULL AND ta.sign_in_status_ IS NOT NULL))
			AND (cs.new_course_id_ IS NULL OR cs.new_course_id_=cs.id_) AND cssp.id_ IS NOT NULL
			AND EXISTS (SELECT id_ FROM course_schedule_teacher_salary WHERE cs.id_=course_schedule_id_ AND settlement_time_ IS NULL)
			<if test="date!=null">
				AND cs.class_date_=#{date}
			</if>
			<if test="dates!=null and dates.size()>0">
				AND cs.class_date_ IN
				<foreach collection="dates" item="dt" separator="," open="(" close=")">
					#{dt}
				</foreach>
			</if>
	</select>

	<select id="getNoAttendance" resultMap="IndexErrDataRecord">
		SELECT
			cs.id_ data_id_,
			cs.organ_id_,
			cs.tenant_id_,
			CASE cs.group_type_ WHEN 'MUSIC' THEN mg.educational_teacher_id_ WHEN 'VIP' THEN vg.educational_teacher_id_ WHEN 'PRACTICE' THEN pg.educational_teacher_id_ END deal_user_id_
		FROM course_schedule cs
			LEFT JOIN teacher_attendance ta ON ta.course_schedule_id_ = cs.id_
			LEFT JOIN music_group mg ON cs.music_group_id_=mg.id_ AND cs.group_type_='MUSIC'
			LEFT JOIN vip_group vg ON cs.music_group_id_=vg.id_ AND cs.group_type_='VIP'
			LEFT JOIN practice_group pg ON cs.music_group_id_=pg.id_ AND cs.group_type_='PRACTICE'
		WHERE ta.teacher_id_ = cs.actual_teacher_id_
			AND cs.status_ = 'OVER' AND cs.del_flag_ = 0 AND cs.class_date_>='2021-02-01'
			AND ta.sign_in_time_ IS NULL AND ta.sign_out_time_ IS NULL AND ta.dispose_content_ IS NULL
			AND (cs.new_course_id_ IS NULL OR cs.new_course_id_ = cs.id_)
			AND EXISTS (SELECT id_ FROM course_schedule_teacher_salary WHERE cs.id_=course_schedule_id_ AND settlement_time_ IS NULL)
			<if test="date!=null">
		  		AND cs.class_date_=#{date}
			</if>
			<if test="dates!=null and dates.size()>0">
				AND cs.class_date_ IN
				<foreach collection="dates" item="dt" separator="," open="(" close=")">
					#{dt}
				</foreach>
			</if>
	</select>

	<sql id="queryErrStringsCondition">
		<where>
			tenant_id_ = #{tenantId}
			<if test="queryOrganIds!=null and queryOrganIds.size()>0">
				AND organ_id_ IN
				<foreach collection="queryOrganIds" item="organId" open="(" close=")" separator=",">
					#{organId}
				</foreach>
			</if>
			<if test="startDate!=null and startDate!=''">
				AND generate_time_&gt;=#{startDate}
			</if>
			<if test="endDate!=null and endDate!=''">
				AND generate_time_&lt;=#{endDate}
			</if>
			<if test="educationUserId!=null">
				AND deal_user_id_ = #{educationUserId}
			</if>
		</where>
	</sql>

	<select id="queryErrStrings" resultType="string">
		SELECT CONCAT(generate_time_, organ_id_, deal_user_id_) FROM index_err_data_record
		<include refid="queryErrStringsCondition"></include>
		GROUP BY generate_time_,organ_id_,deal_user_id_
		ORDER BY generate_time_ DESC,organ_id_,deal_user_id_
		<include refid="global.limit"></include>
	</select>

	<select id="queryErrStringsCount" resultType="int">
		SELECT COUNT(DISTINCT CONCAT(generate_time_,organ_id_,deal_user_id_)) FROM index_err_data_record
		<include refid="queryErrStringsCondition"></include>
	</select>

	<select id="getWithOrganMonthUserStr" resultMap="IndexErrDataRecord">
		SELECT * FROM index_err_data_record WHERE CONCAT(generate_time_, organ_id_, deal_user_id_) IN
		<foreach collection="omuStrings" item="omuString" open="(" close=")" separator=",">
			#{omuString}
		</foreach>
	</select>

	<select id="getAllErrDatas" resultMap="IndexErrDataRecord">
		SELECT * FROM index_err_data_record
		<include refid="queryErrStringsCondition"></include>
	</select>

	<select id="queryErrInspectionData" resultMap="com.ym.mec.biz.dal.dao.InspectionItemPlanDao.InspectionItemPlan" parameterType="map">
		SELECT iip.*,su.real_name_ realName,o.name_ organName,co.name_ cooperationName,mg.name_ musicGroupName,
		       irdr.generate_time_ generateTime,irdr.deal_time_ dealTime
		FROM index_err_data_record irdr
		LEFT JOIN inspection_item_plan iip ON irdr.data_id_=iip.id_
		LEFT JOIN sys_user su ON su.id_ = iip.user_id_
		LEFT JOIN organization o ON o.id_=iip.organ_id_
		LEFT JOIN cooperation_organ co ON co.id_=iip.cooperation_organ_id_
		LEFT JOIN music_group mg ON mg.id_ = iip.music_group_id_
		<include refid="queryErrInspectionDataCondition"/>
		ORDER BY iip.plan_start_ ASC
		<include refid="global.limit"/>
	</select>

	<select id="errInspectionDataCount" resultType="int">
		SELECT COUNT(*)
		FROM index_err_data_record irdr
		LEFT JOIN inspection_item_plan iip ON irdr.data_id_=iip.id_
		LEFT JOIN music_group mg ON mg.id_ = iip.music_group_id_
		<include refid="queryErrInspectionDataCondition"/>
	</select>

	<sql id="queryErrInspectionDataCondition">
		<where>
			irdr.tenant_id_ = #{tenantId}
			<if test="searchType != null and searchType != ''">
				<if test="searchType == 'MUSIC_PATROL_ITEM'">
					and irdr.data_type_ = 'MUSIC_PATROL_ITEM'
				</if>
				<if test="searchType == 'INSPECTION_ITEM_PLAN'">
					and irdr.data_type_ = 'INSPECTION_ITEM_PLAN'
				</if>
			</if>
			<if test="hasDealTime!=null and hasDealTime">
				AND irdr.deal_time_ IS NOT NULL
			</if>
			<if test="hasDealTime!=null and !hasDealTime">
				AND irdr.deal_time_ IS NULL
			</if>
			<if test="itemId != null">
				AND iip.item_id_ = #{itemId}
			</if>
			<if test="userId != null">
				AND iip.user_id_ = #{userId}
			</if>
			<if test="organId != null">
				AND FIND_IN_SET(iip.organ_id_,#{organId})
			</if>
			<if test="cooperationOrganId != null">
				AND iip.cooperation_organ_id_ = #{cooperationOrganId}
			</if>
			<if test="musicGroupId != null">
				AND iip.music_group_id_ = #{musicGroupId}
			</if>
			<if test="conclusionStatus != null">
				AND iip.conclusion_status_ = #{conclusionStatus}
			</if>
			<if test="status !=null">
				AND iip.status_ = #{status}
			</if>
			<if test='hasSubmit != null and hasSubmit.toString()=="1".toString()'>
				AND iip.status_ > 0
			</if>
			<if test='hasSubmit != null and hasSubmit.toString()=="0".toString()'>
				AND iip.status_ = 0
			</if>
			<if test="startTime != null">
				AND iip.plan_start_ >= #{startTime}
			</if>
			<if test="endTime != null">
				<![CDATA[AND iip.plan_start_ <= #{endTime}]]>
			</if>
			<if test="generateStartTime != null">
				AND irdr.generate_time_ >= #{generateStartTime}
			</if>
			<if test="generateEndTime != null">
				<![CDATA[AND irdr.generate_time_ <= #{generateEndTime}]]>
			</if>
			<if test="dealStartTime != null">
				AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") >= #{dealStartTime}
			</if>
			<if test="dealEndTime != null">
				<![CDATA[AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") <= #{dealEndTime}]]>
			</if>
			<if test="search != null">
				AND mg.name_ LIKE CONCAT('%', #{search}, '%')
			</if>
			<if test="ids != null and ids != ''">
				AND FIND_IN_SET(iip.id_,#{ids})
			</if>
		</where>
	</sql>

	<select id="queryErrStudentVisit" resultMap="com.ym.mec.biz.dal.dao.InspectionItemDao.InspectionItem" parameterType="map">
		SELECT ii.*,i.month_ month,o.name_ organName,su.real_name_ userName,
			irdr.generate_time_ generateTime,irdr.deal_time_ dealTime
		FROM index_err_data_record irdr
		LEFT JOIN inspection_item ii ON irdr.data_id_=ii.id_
		LEFT JOIN inspection i ON i.id_ = ii.inspection_id_
		LEFT JOIN organization o ON o.id_=ii.organ_id_
		LEFT JOIN sys_user su ON su.id_ = ii.user_id_
		<include refid="queryErrStudentVisitCondition"/>
		ORDER BY i.month_ DESC ,ii.id_ DESC
		<include refid="global.limit"/>
	</select>

	<!-- 查询当前表的总记录数 -->
	<select id="studentVisitCount" resultType="int">
		SELECT COUNT(*)
		FROM index_err_data_record irdr
		LEFT JOIN inspection_item ii ON irdr.data_id_=ii.id_
		LEFT JOIN inspection i ON i.id_ = ii.inspection_id_
		<include refid="queryErrStudentVisitCondition"/>
	</select>

	<sql id="queryErrStudentVisitCondition">
		<where>
			tenant_id_ = #{tenantId} and
			irdr.data_type_ = 'STUDENT_VISIT'
			<if test="hasDealTime!=null and hasDealTime">
				AND irdr.deal_time_ IS NOT NULL
			</if>
			<if test="hasDealTime!=null and !hasDealTime">
				AND irdr.deal_time_ IS NULL
			</if>
			<if test="organIds != null and organIds != ''">
				AND FIND_IN_SET(ii.organ_id_,#{organIds})
			</if>
			<if test="educationUserId != null">
				AND ii.user_id_ = #{educationUserId}
			</if>
			<if test="operation != null">
				AND ii.operation_ = #{operation}
			</if>
			<if test="ids != null">
				AND FIND_IN_SET(ii.id_,#{ids})
			</if>
			<if test="startTime != null">
				AND i.month_ >= #{startTime}
			</if>
			<if test="endTime != null">
				<![CDATA[AND i.month_ <= #{endTime}]]>
			</if>
			<if test="generateStartTime != null">
				AND irdr.generate_time_ >= #{generateStartTime}
			</if>
			<if test="generateEndTime != null">
				<![CDATA[AND irdr.generate_time_ <= #{generateEndTime}]]>
			</if>
			<if test="dealStartTime != null">
				AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") >= #{dealStartTime}
			</if>
			<if test="dealEndTime != null">
				<![CDATA[AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") <= #{dealEndTime}]]>
			</if>
		</where>
	</sql>

	<select id="queryHistoryErrCourseData" resultMap="com.ym.mec.biz.dal.dao.CourseScheduleDao.CourseScheduleEndDto">
		SELECT
			cs.id_ id_,
			cs.new_course_id_,
			cs.group_type_,
			cs.music_group_id_,
			cs.class_group_id_,
			cs.status_,
			cs.name_,
			cs.class_date_,
			CONCAT(cs.class_date_,' ',cs.start_class_time_) course_start_time_,
			CONCAT(cs.class_date_,' ',cs.end_class_time_) course_end_time_,
			cs.actual_teacher_id_,
			cs.teach_mode_,
			cs.type_,
			cs.schoole_id_,
			cs.create_time_,
			cs.is_lock_,
			cs.organ_id_,
			s.name_ schoole_name_,
			o.name_ organ_name_,
			irdr.generate_time_ generateTime,
		    irdr.deal_time_ dealTime,
		    eu.real_name_ educationTeacherName,
			CASE WHEN COUNT(CASE WHEN sa.id_ IS NULL OR (sa.status_ = 'LEAVE' AND sa.remark_ IS NOT NULL) THEN NULL ELSE 1 END) > 0 THEN 1 ELSE 0 END isCallNames,
			CASE WHEN COUNT(CASE WHEN ta.is_complaints_ = 1 THEN 1 ELSE NULL END) > 0 THEN '1' ELSE '0' END is_complaints_
		FROM index_err_data_record irdr
			LEFT JOIN course_schedule cs ON irdr.data_id_=cs.id_
			LEFT JOIN school s ON cs.schoole_id_=s.id_
			LEFT JOIN organization o ON cs.organ_id_=o.id_
			LEFT JOIN course_schedule_teacher_salary csts ON csts.course_schedule_id_ = cs.id_
			left join teacher_attendance ta on ta.course_schedule_id_ = cs.id_
			<if test="searchType == 'ERR_ATTENDANCE'">
				LEFT JOIN course_schedule_student_payment cssp ON cssp.course_schedule_id_ = cs.id_
			</if>
			LEFT JOIN student_attendance sa ON sa.course_schedule_id_ = cs.id_
			<if test="searchType == 'ERR_ATTENDANCE'">
				AND cssp.user_id_ = sa.user_id_
			</if>
			LEFT JOIN sys_user eu ON irdr.deal_user_id_ = eu.id_
		<include refid="endFindCourseSchedulesCondition"/>
		GROUP BY cs.class_date_,cs.start_class_time_,cs.id_
		ORDER BY cs.class_date_,cs.start_class_time_,cs.id_
		<include refid="global.limit"/>
	</select>

	<select id="historyErrCourseDataCount" resultType="int">
		SELECT
			COUNT(DISTINCT cs.id_)
		FROM index_err_data_record irdr
			LEFT JOIN course_schedule cs ON irdr.data_id_=cs.id_
			<if test="searchType == 'ERR_ATTENDANCE'">
				LEFT JOIN course_schedule_student_payment cssp ON cssp.course_schedule_id_ = cs.id_
			</if>
			LEFT JOIN student_attendance sa ON sa.course_schedule_id_ = cs.id_
			<if test="searchType == 'ERR_ATTENDANCE'">
				AND cssp.user_id_ = sa.user_id_
			</if>
			LEFT JOIN teacher_attendance ta on ta.course_schedule_id_ = cs.id_
			LEFT JOIN course_schedule_teacher_salary csts ON csts.course_schedule_id_ = cs.id_
		<include refid="endFindCourseSchedulesCondition"/>
	</select>

	<select id="getAllDealUserIds" resultType="int">
		SELECT DISTINCT deal_user_id_ FROM index_err_data_record
		<where>
			tenant_id_ = #{tenantId}
			<if test="organIds!=null and organIds.size()>0">
				AND organ_id_ IN
				<foreach collection="organIds" item="organId" open="(" close=")" separator=",">
					#{organId}
				</foreach>
			</if>
		</where>
	</select>

	<sql id="endFindCourseSchedulesCondition">
		<where>
			irdr.tenant_id_ = #{tenantId}
			<if test="searchType == 'ERR_ATTENDANCE'">
				AND irdr.data_type_ = 'TEACHER_EXCEPTION_ATTENDANCE'
			</if>
			<if test="searchType == 'NO_ATTENDANCE'">
				AND irdr.data_type_ = 'TEACHER_NOT_A_CLASS'
			</if>
			<if test="hasDealTime!=null and hasDealTime">
				AND irdr.deal_time_ IS NOT NULL
			</if>
			<if test="hasDealTime!=null and !hasDealTime">
				AND irdr.deal_time_ IS NULL
			</if>
			<if test="educationUserId!=null">
				AND irdr.deal_user_id_ = #{educationUserId}
			</if>
			<if test="mergeCourseType != null and mergeCourseType != ''">
				<if test="mergeCourseType == 'MASTER'">
					AND cs.new_course_id_ = cs.id_
				</if>
				<if test="mergeCourseType == 'ASSIST'">
					AND cs.new_course_id_ > 0 AND cs.new_course_id_ != cs.id_
				</if>
				<if test="mergeCourseType == 'ALL'">
					AND cs.new_course_id_ > 0
				</if>
			</if>
			<if test="isCallNames != null and isCallNames == 1">
				AND sa.id_ IS NOT NULL
			</if>
			<if test="isCallNames != null and isCallNames == 0">
				AND sa.id_ IS NULL
			</if>
			<if test="startTime!=null">
				AND cs.class_date_ &gt;= DATE_FORMAT(#{startTime},"%Y-%m-%d")
			</if>
			<if test="endTime!=null">
				AND cs.class_date_ &lt;= DATE_FORMAT(#{endTime},"%Y-%m-%d")
			</if>
			<if test="generateStartTime != null">
				AND irdr.generate_time_ >= #{generateStartTime}
			</if>
			<if test="generateEndTime != null">
				<![CDATA[AND irdr.generate_time_ <= #{generateEndTime}]]>
			</if>
			<if test="dealStartTime != null">
				AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") >= #{dealStartTime}
			</if>
			<if test="dealEndTime != null">
				<![CDATA[AND DATE_FORMAT(irdr.deal_time_,"%Y-%m-%d") <= #{dealEndTime}]]>
			</if>
			<if test="courseStatus!=null">
				AND cs.status_ = #{courseStatus,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
			<if test="createStartDate!=null">
				AND date(cs.create_time_) &gt;= date(#{createStartDate})
			</if>
			<if test="createEndDate!=null">
				AND date(cs.create_time_) &lt;= date(#{createEndDate})
			</if>
			<if test="courseType!=null">
				AND cs.type_ = #{courseType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
			<if test="groupType!=null">
				AND cs.group_type_ = #{groupType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
			<if test="schoolId!=null">
				AND cs.schoole_id_ = #{schoolId}
			</if>
			<if test="teachMode!=null">
				AND cs.teach_mode_ = #{teachMode,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
			<if test="teacherIdList != null">
				AND csts.user_id_=#{teacherIdList}
			</if>
			<if test="teachType != null">
				AND csts.teacher_role_ = #{teachType,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler}
			</if>
			<if test="organIdList!=null">
				AND FIND_IN_SET(cs.organ_id_,#{organIdList})
			</if>
			<if test="search != null and search != ''">
				AND (cs.music_group_id_ = #{search} OR cs.id_=#{search} OR cs.name_ LIKE CONCAT('%' ,#{search}, '%' ))
			</if>
			<if test="courseIdSearch != null">
				AND cs.id_ = #{courseIdSearch}
			</if>
			<if test="classGroupIds != null and classGroupIds.size() > 0">
				AND cs.class_group_id_ IN
				<foreach collection="classGroupIds" item="classGroupId" open="(" close=")" separator=",">
					#{classGroupId}
				</foreach>
			</if>
		</where>
	</sql>
</mapper>
