<?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.zcloud.mapper.dsno2.paper.StageexampaperMapper">

	<!--表名 -->
	<sql id="tableName">
		BUS_STAGEEXAMPAPER
	</sql>

	<!--数据字典表名 -->
	<sql id="dicTableName">
		SYS_DICTIONARIES
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		f.EXAMNAME,
		f.EXAMTIME,
		f.ANSWERSHEETTIME,
		f.EXAMSCORE,
		f.QUESTIONNUM,
		f.EXAMSTATE,
		f.CREATOR,
		f.CREATTIME,
		f.OPERATOR,
		f.OPERATTIME,
		f.ISDELETE,
		f.CORPINFO_ID,
		f.CURRICULUM_ID,
		f.STUDYTASK_ID,
		f.PASSSCORE,
		f.INPUT_ID,
		f.NUMBER_OF_EXAMINATIONS,
		f.STAGEEXAMPAPER_ID
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		EXAMNAME,
		EXAMTIME,
		ANSWERSHEETTIME,
		EXAMSCORE,
		QUESTIONNUM,
		EXAMSTATE,
		CREATOR,
		CREATTIME,
		OPERATOR,
		OPERATTIME,
		ISDELETE,
		CORPINFO_ID,
		STUDYTASK_ID,
		PASSSCORE,
		INPUT_ID,
		NUMBER_OF_EXAMINATIONS,
		STAGEEXAMPAPER_ID
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{EXAMNAME},
		#{EXAMTIME},
		#{ANSWERSHEETTIME},
		#{EXAMSCORE},
		#{QUESTIONNUM},
		#{EXAMSTATE},
		#{CREATOR},
		#{CREATTIME},
		#{OPERATOR},
		#{OPERATTIME},
		#{ISDELETE},
		#{CORPINFO_ID},
		#{STUDYTASK_ID},
		#{PASSSCORE},
		#{INPUT_ID},
		#{NUMBER_OF_EXAMINATIONS},
		#{STAGEEXAMPAPER_ID}
	</sql>

	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into
		<include refid="tableName"></include>
		(
		<include refid="Field2"></include>
		) values (
		<include refid="FieldValue"></include>
		)
	</insert>

	<!-- 删除-->
	<delete id="delete" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISDELETE = '1',
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME}
		where
		STAGEEXAMPAPER_ID = #{STAGEEXAMPAPER_ID}
	</delete>

	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		EXAMNAME = #{EXAMNAME},
		EXAMTIME = #{EXAMTIME},
		ANSWERSHEETTIME = #{ANSWERSHEETTIME},
		EXAMSCORE = #{EXAMSCORE},
		QUESTIONNUM = #{QUESTIONNUM},
		EXAMSTATE = #{EXAMSTATE},
		CURRICULUM_ID = #{CURRICULUM_ID},
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME},
		STAGEEXAMPAPER_ID = STAGEEXAMPAPER_ID
		where
		STAGEEXAMPAPER_ID = #{STAGEEXAMPAPER_ID}
	</update>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where
		f.STAGEEXAMPAPER_ID = #{STAGEEXAMPAPER_ID}
	</select>

	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
		select
		COUNT(DISTINCT r.USER_ID) USERCOUNT,
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		 LEFT JOIN BUS_STAGESTUDENTRELATION r ON r.STAGEEXAMPAPER_ID = f.STAGEEXAMPAPER_ID and r.ISDELETE = '0'
		where f.ISDELETE = '0' and f.CORPINFO_ID = #{pd.CORPINFO_ID}
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
			(
				f.EXAMNAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
			<!--	根据需求自己加检索条件
                字段1 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
                 or
                字段2 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
            -->
			)
		</if>
		GROUP BY f.STAGEEXAMPAPER_ID ORDER BY f.OPERATTIME DESC
	</select>

	<!-- 列表（课程为主体的列表信息） -->
	<select id="listCurriculumPage" parameterType="page" resultType="pd">
		select
		f.CURRICULUMNAME,
		f.CURRICULUMINTRODUCE,
		f.CURRICULUMAIM,
		f.STARTTIME,
		f.ENDTIME,
		f.CURRICULUM_ID
		,COUNT(m.KCMIDDLEKJ_ID) COURSEWARECOUNT
		,p.STAGEEXAMPAPER_ID
		,p.EXAMNAME
		from
		BUS_CURRICULUM f
		LEFT JOIN BUS_KCMIDDLEKJ m ON m.CURRICULUMID = f.CURRICULUM_ID AND m.ISDELETE = '0'
		LEFT JOIN <include refid="tableName"></include> p ON p.CURRICULUM_ID = f.CURRICULUM_ID AND p.ISDELETE = '0'
		where f.ISDELETE = '0' and f.CORPINFO_ID = #{pd.CORPINFO_ID}
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
			(
			<!--	根据需求自己加检索条件
                字段1 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
                 or
                字段2 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
            -->
			)
		</if>
		GROUP BY f.CURRICULUM_ID ORDER BY f.OPERATTIME DESC
	</select>

	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		 ,i.EXAMNAME as shijuanName
		from
		<include refid="tableName"></include> f
		LEFT JOIN bus_stageexampaper_input i on f.INPUT_ID = i.STAGEEXAMPAPERINPUT_ID
		where f.ISDELETE = '0' and f.CORPINFO_ID = #{CORPINFO_ID}
		<if test="STUDYTASK_ID != null and STUDYTASK_ID != ''"><!-- 关键词检索 -->
			and STUDYTASK_ID = #{STUDYTASK_ID}
		</if>
	</select>

	<!-- 批量删除 -->
	<delete id="deleteAll" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISDELETE = '1',
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME}
		where
		STAGEEXAMPAPER_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
			#{item}
		</foreach>
	</delete>

	<!-- 列表(根据所选ID获取数据) -->
	<select id="findByIds" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where
		STAGEEXAMPAPER_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<!-- 删除(根据课程ID删除试题数据)-->
	<delete id="deleteByCurriculumIds" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISDELETE = '1',
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME}
		where ISDELETE = '0'
		AND CURRICULUM_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
			#{item}
		</foreach>
	</delete>

	<!-- 通过课程ID获取数据 -->
	<select id="findByCurriculum" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where f.ISDELETE = '0' AND
		f.CURRICULUM_ID = #{CURRICULUM_ID}
	</select>

	<!-- 修改（考试开考或者考试人员已考试，试卷不能再变动） -->
	<update id="updateExamState" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		EXAMSTATE = #{EXAMSTATE},
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME}
		where
		STAGEEXAMPAPER_ID = #{STAGEEXAMPAPER_ID}
	</update>

<!--	&lt;!&ndash; 列表（考试结果列表） &ndash;&gt;-->
<!--	<select id="listSummarylistPage" parameterType="page" resultType="pd">-->
<!--		select-->
<!--		COUNT((CASE WHEN (r.STAGEEXAMSTATE = '3') THEN r.USER_ID END) ) COMPLETENUM,-->
<!--		COUNT(r.USER_ID) PARTICIPANTNUM,-->
<!--		<include refid="Field"></include>-->
<!--		from-->
<!--		<include refid="tableName"></include> f-->
<!--		LEFT JOIN BUS_STAGESTUDENTRELATION r ON r.STAGEEXAMPAPER_ID = f.STAGEEXAMPAPER_ID and r.ISDELETE = '0'-->
<!--		where f.ISDELETE = '0' and f.CORPINFO_ID = #{pd.CORPINFO_ID}-->
<!--		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''">&lt;!&ndash; 关键词检索 &ndash;&gt;-->
<!--			and-->
<!--			(-->
<!--			f.EXAMNAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')-->
<!--			&lt;!&ndash;	根据需求自己加检索条件-->
<!--                字段1 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')-->
<!--                 or-->
<!--                字段2 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')-->
<!--            &ndash;&gt;-->
<!--			)-->
<!--		</if>-->
<!--		<if test="pd.STARTTIME != null and pd.STARTTIME != ''">&lt;!&ndash; 关键词检索 &ndash;&gt;-->
<!--			and f.EXAMTIME &gt;= #{pd.STARTTIME}-->
<!--		</if>-->
<!--		<if test="pd.ENDTIME != null and pd.ENDTIME != ''">-->
<!--			and f.EXAMTIME &lt;= #{pd.ENDTIME}-->
<!--		</if>-->
<!--		GROUP BY f.STAGEEXAMPAPER_ID ORDER BY f.OPERATTIME DESC-->
<!--	</select>-->

</mapper>
