<?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.yonge.cooleshow.biz.dal.dao.PracticeDao">
	<select id="selectTeacherPactice" resultType="com.yonge.cooleshow.biz.dal.vo.PracticeVo">
		SELECT
			u.id_ AS userId,
			u.username_ AS userName,
			u.real_name_ AS realName,
			u.phone_ AS phone,
			s.name_ AS subjectName,
			p.course_id_ AS courseId,
			p.order_no_ AS orderNo,
			p.original_price_ AS originalPrice,
			p.expect_price_ AS expectPrice,
			p.actual_price_ AS actualPrice,
			p.created_time_ AS paymentTime,
			cs.class_date_ AS classDate,
			cs.start_time_ AS startTime,
			cs.end_time_ AS endTime,
			cs.status_ AS `status`
		FROM course_schedule_student_payment p
		LEFT JOIN sys_user u ON p.user_id_ = u.id_
		LEFT JOIN `subject` s ON p.course_group_id_ = s.id_
		LEFT JOIN course_schedule cs ON p.course_id_ = cs.id_
		WHERE p.course_id_ IN (SELECT id_ FROM course_schedule WHERE teacher_id_ = #{param.teacherId} AND type_ = 'PRACTICE')
		<if test="null != param.search and '' != param.search">
			AND (
			cs.id_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.id_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.username_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.phone_ LIKE CONCAT('%', #{param.search}, '%')
			)
		</if>
		<if test="null != param.orderNo and '' != param.orderNo">
			AND p.order_no_ = #{param.orderNo}
		</if>
		<if test="null != param.subjectId">
			AND s.id_ = #{param.subjectId}
		</if>
		<if test="null != param.status and '' != param.status">
			AND cs.status_ = #{param.status}
		</if>
		<if test="param.startTime !=null">
			<![CDATA[AND p.created_time_ >= #{param.startTime} ]]>
		</if>
		<if test="param.endTime !=null">
			<![CDATA[AND p.created_time_ <= #{param.endTime} ]]>
		</if>
	</select>
	<select id="selectStudentPactice" resultType="com.yonge.cooleshow.biz.dal.vo.PracticeVo">
		SELECT
			u.id_ AS userId,
			u.username_ AS userName,
			u.real_name_ AS realName,
			u.phone_ AS phone,
			s.name_ AS subjectName,
			p.course_id_ AS courseId,
			p.order_no_ AS orderNo,
			p.original_price_ AS originalPrice,
			p.expect_price_ AS expectPrice,
			p.actual_price_ AS actualPrice,
			p.created_time_ AS paymentTime,
			cs.class_date_ AS classDate,
			cs.start_time_ AS startTime,
			cs.end_time_ AS endTime,
			cs.status_ AS `status`
		FROM course_schedule cs
		LEFT JOIN course_schedule_student_payment p ON cs.id_ = p.course_id_
		LEFT JOIN sys_user u ON cs.teacher_id_ = u.id_
		LEFT JOIN `subject` s ON p.course_group_id_ = s.id_
		WHERE cs.id_ IN (SELECT course_id_ FROM course_schedule_student_payment WHERE user_id_ = #{param.studentId} AND course_type_ = 'PRACTICE')
		<if test="null != param.search and '' != param.search">
			AND (
			cs.id_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.id_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.username_ LIKE CONCAT('%', #{param.search}, '%') OR
			u.phone_ LIKE CONCAT('%', #{param.search}, '%')
			)
		</if>
		<if test="null != param.orderNo and '' != param.orderNo">
			AND p.order_no_ = #{param.orderNo}
		</if>
		<if test="null != param.status and '' != param.status">
			AND cs.status_ = #{param.status}
		</if>
		<if test="null != param.subjectId">
			AND s.id_ = #{param.subjectId}
		</if>
		<if test="param.startTime !=null">
			<![CDATA[AND p.created_time_ >= #{param.startTime} ]]>
		</if>
		<if test="param.endTime !=null">
			<![CDATA[AND p.created_time_ <= #{param.endTime} ]]>
		</if>
	</select>
</mapper>