<?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">
<!-- 命名，每一个映射对象不一样
	namespace:必须与对应的接口全类名一致
 -->
<mapper namespace="com.iamberry.wechat.service.mapper.KeyWordMapper">
	<resultMap type="KeyWord" id="toKeyWord">
		<id column="keyServiceNo" property="keyServiceNo"/>
		<result column="createTime" property="createTime"/>
		<result column="keyType" property="keyType"/>
		<result column="keyWord" property="keyWord"/>
		<result column="reType" property="reType"/>
		<result column="refImageTextId" property="refImageTextId"/>
		<result column="refText" property="refText"/>
		<result column="refVedioId" property="refVedioId"/>
		<result column="title" property="title"/>
		<result column="vchar1" property="vchar1"/>
		<result column="vchar2" property="vchar2"/>
		<result column="vchar3" property="vchar3"/>
		<result column="vchar4" property="vchar4"/>
		<result column="wxpublicNo" property="wxpublicNo"/>
	</resultMap>
	
	<select id="selectOne" parameterType="KeyWord" resultMap="toKeyWord">
		SELECT * FROM tb_wx_keyword WHERE keyserviceNo=#{0}
	</select>
	
	<select id="getByKeyWord" parameterType="KeyWord" resultMap="toKeyWord">
		SELECT 
			* 
		FROM 
			TB_WX_KEYWORD 
		WHERE 
			KEYWORD LIKE CONCAT('%', #{keyWord},'%')
		LIMIT 0,1
	</select>
	
	<!-- 查询关键字列表 -->
  	<select id="selectKeyList" parameterType="PaperBean" resultMap="toKeyWord">
   	 	SELECT * FROM tb_wx_keyword WHERE wxpublicNo=#{text}
   	 	<if test="str != '' and str != null">
   	 		AND keyWord LIKE CONCAT(CONCAT('%',#{str}),'%')
   	 	</if>
   	 	ORDER BY keyServiceNo DESC LIMIT #{minNum}, #{maxNum}
  	</select>
  	
  	<!-- 查询关键字的数量 -->
  	<select id="selectKeyListCount" parameterType="PaperBean" resultType="int">
  		SELECT COUNT(keyServiceNo) FROM tb_wx_keyword WHERE wxpublicNo=#{text}
  		<if test="str != '' and str != null">
   	 		AND keyWord LIKE CONCAT(CONCAT('%',#{str}),'%')
   	 	</if>
  	</select>
  
  <!-- 批量删除关键字， 根据ID -->
  <select id="batchDelKeyWordById" parameterType="java.util.List">
  	DELETE FROM tb_wx_keyword WHERE 
  	<foreach collection="list" item="delId" separator="OR">
  		keyServiceNo = #{delId}
  	</foreach>
  </select>
  
  <insert id="insert">
  	insert into tb_wx_keyword (keyServiceNo, createTime, keyType, 
      keyWord, reType, refImageTextId, 
      refText, refVedioId, title, 
      vchar1, vchar2, vchar3, 
      vchar4, wxpublicNo)
    values (#{keyServiceNo,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP}, #{keyType,jdbcType=VARCHAR}, 
      #{keyWord,jdbcType=VARCHAR}, #{reType,jdbcType=VARCHAR}, #{refImageTextId,jdbcType=BIGINT}, 
      #{refText,jdbcType=VARCHAR}, #{refVedioId,jdbcType=VARCHAR}, #{title,jdbcType=VARCHAR}, 
      #{vchar1,jdbcType=VARCHAR}, #{vchar2,jdbcType=VARCHAR}, #{vchar3,jdbcType=VARCHAR}, 
      #{vchar4,jdbcType=VARCHAR}, #{wxpublicNo,jdbcType=VARCHAR})
  </insert>
  <update id="updateByPrimaryKey">
    update tb_wx_keyword
    set createTime = #{createTime,jdbcType=TIMESTAMP},
      keyType = #{keyType,jdbcType=VARCHAR},
      keyWord = #{keyWord,jdbcType=VARCHAR},
      reType = #{reType,jdbcType=VARCHAR},
      refImageTextId = #{refImageTextId,jdbcType=BIGINT},
      refText = #{refText,jdbcType=VARCHAR},
      refVedioId = #{refVedioId,jdbcType=VARCHAR},
      title = #{title,jdbcType=VARCHAR},
      vchar1 = #{vchar1,jdbcType=VARCHAR},
      vchar2 = #{vchar2,jdbcType=VARCHAR},
      vchar3 = #{vchar3,jdbcType=VARCHAR},
      vchar4 = #{vchar4,jdbcType=VARCHAR},
      wxpublicNo = #{wxpublicNo,jdbcType=VARCHAR}
    where keyServiceNo = #{keyServiceNo,jdbcType=BIGINT}
  </update>
  <delete id="deleteKeyWordById" parameterType="java.lang.Long">
    DELETE FROM tb_wx_keyword
    WHERE keyServiceNo = #{keyServiceNo,jdbcType=BIGINT}
  </delete>
  
  <!-- 查询关键字的总数 -->
  <select id="selectAllKeyWordNum" parameterType="KeyWord" resultType="int">
  	select count(*) from tb_wx_keyword where wxpublicNo=#{0}
  </select>
  
  <!-- 根据公众号和当前的页数来查询,每页分多少条 -->
  <select id="selectKeyWordPageList" parameterType="PagingDTO" resultMap="toKeyWord">
  	select * from tb_wx_keyword where wxpublicNo=#{wxPublicNo} ORDER BY keyServiceNo DESC limit #{pageNo}, #{selectNum} 
  </select>
</mapper>
