<?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.pcloud.book.guide.mapper.PcloudGuideMapper" >
  <resultMap id="BaseResultMap" type="com.pcloud.book.guide.entity.PcloudGuide" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="type" property="type" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="robot_wx_id" property="robotWxId" jdbcType="VARCHAR" />
    <result column="time_span" property="timeSpan" jdbcType="INTEGER" />
    <result column="seq_num" property="seqNum" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, type, create_time, update_time, robot_wx_id, time_span, seq_num
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from pcloud_guide
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from pcloud_guide
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.pcloud.book.guide.entity.PcloudGuide" useGeneratedKeys="true" keyProperty="id">
    insert into pcloud_guide (type, create_time,
      update_time, robot_wx_id, time_span, 
      seq_num, pcloud_classify_id)
    values (#{type,jdbcType=VARCHAR}, now(),
      now(), #{robotWxId,jdbcType=INTEGER}, #{timeSpan,jdbcType=INTEGER},
      #{seqNum,jdbcType=INTEGER}, #{pcloudClassifyId})
  </insert>
  <insert id="insertSelective" parameterType="com.pcloud.book.guide.entity.PcloudGuide" >
    insert into pcloud_guide
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="type != null" >
        type,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
      <if test="updateTime != null" >
        update_time,
      </if>
      <if test="robotWxId != null" >
        robot_wx_id,
      </if>
      <if test="timeSpan != null" >
        time_span,
      </if>
      <if test="seqNum != null" >
        seq_num,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="type != null" >
        #{type,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="robotWxId != null" >
        #{robotWxId,jdbcType=INTEGER},
      </if>
      <if test="timeSpan != null" >
        #{timeSpan,jdbcType=INTEGER},
      </if>
      <if test="seqNum != null" >
        #{seqNum,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.pcloud.book.guide.entity.PcloudGuide" >
    update pcloud_guide
    <set >
      <if test="type != null" >
        type = #{type,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null" >
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="robotWxId != null" >
        robot_wx_id = #{robotWxId,jdbcType=INTEGER},
      </if>
      <if test="timeSpan != null" >
        time_span = #{timeSpan,jdbcType=INTEGER},
      </if>
      <if test="seqNum != null" >
        seq_num = #{seqNum,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.pcloud.book.guide.entity.PcloudGuide" >
    update pcloud_guide
    set update_time = now(),
      time_span = #{timeSpan,jdbcType=INTEGER},
      seq_num = #{seqNum,jdbcType=INTEGER}
    where id = #{id,jdbcType=INTEGER}
  </update>

  <select id="getMaxSeqNum" resultType="Integer">
    select ifnull(max(seq_num), 0) from pcloud_guide
    where 1=1
    <if test="robotWxId != null">
      and robot_wx_id = #{robotWxId}
    </if>
    <if test="pcloudClassifyId != null">
      and pcloud_classify_id = #{pcloudClassifyId}
    </if>
    <if test="robotWxId == null and pcloudClassifyId == null">
      and type = 0
    </if>
  </select>

  <select id="getCount" resultType="Integer">
    SELECT
        ifnull(count(1), 0)
    FROM
        (
            SELECT
                a.id
            FROM
                pcloud_guide a
            LEFT JOIN pcloud_guide_message b ON a.id = b.pcloud_guide_id
            WHERE 1=1
            <if test="robotWxId != null">
              and robot_wx_id = #{robotWxId}
            </if>
            <if test="robotWxId == null and pcloudClassifyId == null">
              and type = 0
            </if>
            <if test="search != null">
              and content like concat('%', #{search}, '%')
            </if>
            <if test="pcloudClassifyId != null">
              and pcloud_classify_id = #{pcloudClassifyId}
            </if>
            GROUP BY a.id
        ) s
  </select>

  <select id="listPcloudGuide" resultType="com.pcloud.book.guide.dto.PcloudGuideDTO">
    SELECT
        a.id pcloudGuideId,
        a.type,
        a.create_time createTime,
        a.time_span timeSpan,
        a.seq_num seqNum
    FROM
        pcloud_guide a
    LEFT JOIN pcloud_guide_message b ON a.id = b.pcloud_guide_id
    WHERE 1=1
    <if test="robotWxId != null">
      and robot_wx_id = #{robotWxId}
    </if>
    <if test="robotWxId == null and pcloudClassifyId == null">
      and type = 0
    </if>
    <if test="search != null">
      and content like concat('%', #{search}, '%')
    </if>
    <if test="pcloudClassifyId != null">
      and pcloud_classify_id = #{pcloudClassifyId}
    </if>
    GROUP BY a.id
    ORDER BY seq_num ASC
    limit #{pageNum}, #{numPerPage}
  </select>

  <update id="updateSeqNum">
    update pcloud_guide set seq_num = #{seqNum} where id = #{id}
  </update>

  <select id="listByRobotId" resultMap="BaseResultMap">
    SELECT
    id, time_span, seq_num
    FROM
        pcloud_guide
    WHERE
        robot_wx_id = #{robotId}
    ORDER BY
        seq_num ASC
  </select>

  <select id="getFirstByRobotId" resultMap="BaseResultMap">
    SELECT
    id, time_span, seq_num
    FROM
        pcloud_guide
    WHERE
        robot_wx_id = #{robotId}
    ORDER BY
        seq_num ASC
    LIMIT #{num}, 1
  </select>

  <select id="listPcloudGuideIds" resultType="Integer">
    select id from pcloud_guide where robot_wx_id = #{robotWxId}
  </select>

  <select id="getFirstByClassifyId" resultMap="BaseResultMap">
    SELECT
    id, time_span, seq_num
    FROM
        pcloud_guide
    WHERE
        pcloud_classify_id = #{pcloudClassifyId}
    ORDER BY
        seq_num ASC
    LIMIT #{num}, 1
  </select>
</mapper>