<?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.cms.dal.dao.HelpCenterContentDao">
	<resultMap id="HelpCenterContent" type="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent">
		<id column="id_" property="id" jdbcType="BIGINT" />
		<result column="title_" property="title" jdbcType="VARCHAR" />
		<result column="create_on_" property="createOn" jdbcType="TIMESTAMP" />
		<result column="modify_on_" property="modifyOn" jdbcType="TIMESTAMP" />
		<result column="release_time_" property="releaseTime" jdbcType="INTEGER" />
		<result column="release_status_" property="releaseStatus" jdbcType="INTEGER" />
		<result column="status_" property="status" jdbcType="INTEGER" />
		<result column="order_" property="order" jdbcType="INTEGER" />
		<result column="catalog_id_" property="catalogId" jdbcType="BIGINT" />
		<result column="catalog_type_" property="catalogType" jdbcType="VARCHAR"/>
		<result column="create_by_" property="createBy" jdbcType="BIGINT" />
		<result column="update_by_" property="updateBy" jdbcType="BIGINT" />
	</resultMap>

	<resultMap id="HelpCenterContentBlobs" type="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent"
		extends="HelpCenterContent">
		<result column="content_" property="content" jdbcType="LONGVARCHAR" />
		<result column="updateName" property="updateName" jdbcType="VARCHAR" />
	</resultMap>
	<!-- 查询条件 -->
	<sql id="queryConditionUnion">
		<where>
			<if test="catalogIds != null">
				and content.catalog_id_ in (${catalogIds})
			</if>
			<if test="title!= null and title != ''">
				and content.title_ like '%' #{title} '%'
			</if>
			<if test="status!= null">
			and ((content.status_ = #{status} and content.catalog_id_ = 1)
				or (content.status_ = #{status} and content.catalog_id_ = 2 and content.release_time_ &lt;= now()))
			</if>
			<if test="catalogType != null and catalogType != ''">
				and content.catalog_type_ = #{catalogType}
			</if>
		</where>
	</sql>
	
	<sql id="Base_Column_List">
		id_, title_, catalog_id_, create_on_, modify_on_,status_,order_,release_time_,release_status_
	</sql>

	<sql id="Base_Column_List_union">
		content.id_, content.title_, content.catalog_id_,
		content.create_on_,content.release_time_,content.release_status_,
		content.modify_on_,content.status_,content.content_,content.order_,
		catalog.id_,
		catalog.name_, catalog.parent_id_,content.catalog_type_,content.create_by_,content.update_by_
	</sql>

	<sql id="Blob_Column_List">
		content_
	</sql>

	<select id="get" resultMap="HelpCenterContentBlobs">
		select
		<include refid="Base_Column_List_union" />
		from help_center_content content
		left join help_center_catalog catalog on
		content.catalog_id_=catalog.id_
		where content.id_ = #{id}
	</select>

	<select id="queryPage" parameterType="map" resultMap="HelpCenterContentBlobs">
		select
		<include refid="Base_Column_List_union" />
		,su.username_ as updateName
		from help_center_content content
		left join help_center_catalog catalog on content.catalog_id_=catalog.id_
		left join sys_user su on content.update_by_ = su.id_
		<include refid="queryConditionUnion" />
		order by content.order_ asc,content.release_time_ desc,content.id_ desc
		<include refid="global.limit" />
	</select>

	<select id="queryCount" parameterType="map" resultType="int">
		select count(*) from help_center_content content left join help_center_catalog catalog on
		content.catalog_id_=catalog.id_
		<include refid="queryConditionUnion" />
	</select>

	<delete id="delete">
		delete from help_center_content
		where id_ = #{id}
	</delete>
	
	<delete id="deleteContentByCatalog" parameterType="java.lang.Integer">
		delete from help_center_content
		where catalog_id_ = #{catalogId,jdbcType=INTEGER}
	</delete>

	<insert id="insert" parameterType="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent">
		insert into help_center_content
		(id_, title_, catalog_id_,
		create_on_, modify_on_, content_,status_,order_,update_by_,create_by_,catalog_type_,release_time_,release_status_
		)
		values
		(#{id,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR},
		#{catalogId,jdbcType=INTEGER},
		#{createOn,jdbcType=TIMESTAMP},
		#{modifyOn,jdbcType=TIMESTAMP}, #{content,jdbcType=LONGVARCHAR},#{status,jdbcType=INTEGER},#{order,jdbcType=INTEGER},#{createBy},#{updateBy},#{catalogType},#{releaseTime},#{releaseStatus}
		)
	</insert>

	<update id="update" parameterType="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent">
		update help_center_content
		<set>
			<if test="title != null">
				title_ = #{title},
			</if>
			<if test="catalogId != null">
				catalog_id_ =#{catalogId},
			</if>
			<if test="createOn != null">
				create_on_ = #{createOn},
			</if>
			<if test="modifyOn != null">
				modify_on_ = #{modifyOn},
			</if>
			<if test="content != null">
				content_ = #{content},
			</if>
			<if test="status != null">
				status_ = #{status},
			</if>
			<if test="order != null">
				order_ = #{order},
			</if>
			<if test="updateBy != null">
				update_by_ = #{updateBy},
			</if>
			<if test="createBy != null">
				create_by_ = #{createBy},
			</if>
			<if test="releaseStatus != null">
				release_status_ = #{releaseStatus},
			</if>
			<if test="releaseTime != null">
				release_time_ = #{releaseTime},
			</if>
			<if test="catalogType != null and catalogType != ''">
				catalog_type_ = #{catalogType}
			</if>
		</set>
		where id_ = #{id}
	</update>

	<update id="updateBlobs" parameterType="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent">
		update help_center_content
		set title_ = #{title,jdbcType=VARCHAR},
		catalog_id_ =
		#{catalogId,jdbcType=INTEGER},
		create_on_ =
		#{createOn,jdbcType=TIMESTAMP},
		modify_on_ =
		#{modifyOn,jdbcType=TIMESTAMP},
		content_ =
		#{content,jdbcType=LONGVARCHAR},
		status_= #{status,jdbcType=INTEGER},
		release_status_= #{releaseStatus,jdbcType=INTEGER},
		release_time_= #{releaseTime,jdbcType=INTEGER},
		order_= #{order,jdbcType=INTEGER}
		where id_ = #{id,jdbcType=INTEGER}
	</update>

	<update id="updateByKey" parameterType="com.yonge.cooleshow.cms.dal.entity.HelpCenterContent">
		update help_center_content
		set title_ = #{title,jdbcType=VARCHAR},
		catalog_id_ =
		#{catalogId,jdbcType=INTEGER},
		create_on_ =
		#{createOn,jdbcType=TIMESTAMP},
		modify_on_ =
		#{modifyOn,jdbcType=TIMESTAMP},
		status_ = #{status,jdbcType=INTEGER},
		order_ = #{order,jdbcType=INTEGER},
		release_time_ = #{releaseTime,jdbcType=INTEGER},
		release_status_ = #{releaseStatus,jdbcType=INTEGER}
		where id_ = #{id,jdbcType=INTEGER}
	</update>
	
	<update id="updateContentByCatalog" parameterType="map">
		update help_center_content set status_=#{status} where catalog_id_=#{catalogId}
	</update>

	<update id="status">
		update help_center_content set status_=if(status_ =0,1,0) where id_=#{catalogId}

	</update>

	<insert id="noticeReleaseToTeacher">
		insert into sys_message
			(
		user_id_,type_, title_, content_,status_, receiver_, send_time_,  create_on_, modify_on_,  group_, client_id_
			)
		SELECT
			t.user_id_,
			3,
			#{param.title},
			#{param.content},
			2,
			su.phone_,
			#{param.releaseTime},
			now(),now(),'NOTICE','TEACHER'
		from teacher t
		left join sys_user su on su.id_ = t.user_id_
	</insert>

	<insert id="noticeReleaseToStudent">
		insert into sys_message
		(
		user_id_,type_, title_, content_,status_, receiver_, send_time_,  create_on_, modify_on_,  group_, client_id_,message_config_id_
		)
		SELECT
		t.user_id_,
		3,
		#{param.title},
		#{param.content},
		2,
		su.phone_,
		#{param.releaseTime},
		now(),now(),'NOTICE','STUDENT',48
		from student t
		left join sys_user su on su.id_ = t.user_id_
	</insert>

	<select id="selectNeedReleaseNotice"  resultMap="HelpCenterContentBlobs">
		select
		<include refid="Base_Column_List_union" />
		from help_center_content content
		left join help_center_catalog catalog on
		content.catalog_id_=catalog.id_
		where content.status_ = 0
		and content.release_time_ &lt;= date_add(now(),interval 5 MINUTE)
		and content.catalog_id_ = 2
		and content.release_status_ = 0
	</select>

	<select id="selectByNoticeStatus" resultType="java.lang.Integer">
		select count(1)
		from help_center_content hcc
		<where>
			hcc.catalog_id_ = 2
			<if test="status != null">
				and hcc.status_ = #{status}
			</if>
			<if test="catalogType != null and catalogType != ''">
				and hcc.catalog_type_ = #{catalogType}
			</if>
		</where>
	</select>
</mapper>