<?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.pcloudkeyword.dao.impl.PcloudRobotDaoImpl" >
  <resultMap id="BaseResultMap" type="com.pcloud.book.pcloudkeyword.entity.PcloudRobot" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="wx_id" property="wxId" jdbcType="VARCHAR" />
    <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
    <result column="head" property="head" jdbcType="VARCHAR" />
    <result column="qrcode_url" property="qrcodeUrl" jdbcType="VARCHAR" />
      <result column="robot_type" jdbcType="INTEGER" property="robotType" />
      <result column="keyword_classify_id" jdbcType="BIGINT" property="keywordClassifyId" />
    <result column="state" property="state" jdbcType="INTEGER" />
    <result column="real_status" property="realStatus" jdbcType="INTEGER" />
      <result column="wake_up" property="wakeUp" jdbcType="INTEGER" />
    <result column="unique_number" property="uniqueNumber" jdbcType="VARCHAR"/>
      <result column="welcome_duration" property="welcomeDuration" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, wx_id, nick_name, head, qrcode_url, robot_type, keyword_classify_id, state, real_status, wake_up, unique_number,welcome_duration
  </sql>

  <insert id="insert" parameterType="PcloudRobot" useGeneratedKeys="true" keyProperty="id">
    insert into pcloud_robot (
        wx_id,
        nick_name,
        head,
        qrcode_url,
        robot_type,
        keyword_classify_id,
        state,
        real_status,
        wake_up,
        unique_number,
        welcome_duration
      )
    values (
      #{wxId,jdbcType=VARCHAR},
      #{nickName,jdbcType=VARCHAR},
      #{head,jdbcType=VARCHAR},
      #{qrcodeUrl,jdbcType=VARCHAR},
      #{robotType,jdbcType=INTEGER},
      #{keywordClassifyId,jdbcType=BIGINT},
      #{state,jdbcType=INTEGER},
      #{realStatus,jdbcType=INTEGER},
      #{wakeUp,jdbcType=INTEGER},
      #{uniqueNumber, jdbcType=VARCHAR},
      #{welcomeDuration, jdbcType=INTEGER}
      )
  </insert>

    <update id="update" parameterType="PcloudRobot">
        update pcloud_robot
        <set>
            <if test="wxId != null">
                wx_id = #{wxId,jdbcType=VARCHAR},
            </if>
            <if test="nickName != null">
                nick_name = #{nickName,jdbcType=VARCHAR},
            </if>
            <if test="head != null">
                head = #{head,jdbcType=VARCHAR},
            </if>
            <if test="qrcodeUrl != null">
                qrcode_url = #{qrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="robotType != null">
                robot_type = #{robotType,jdbcType=INTEGER},
            </if>
            <if test="keywordClassifyId != null">
                keyword_classify_id = #{keywordClassifyId,jdbcType=BIGINT},
            </if>
            <if test="state != null">
                state = #{state,jdbcType=INTEGER},
            </if>
            <if test="realStatus != null">
                real_status = #{realStatus,jdbcType=INTEGER},
            </if>
            <if test="wakeUp != null">
                wake_up = #{wakeUp,jdbcType=INTEGER},
            </if>
            <if test="uniqueNumber != null">
                unique_number = #{uniqueNumber, jdbcType=VARCHAR},
            </if>
            <if test="welcomeDuration != null">
                welcome_duration = #{welcomeDuration,jdbcType=INTEGER},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <update id="updatePcloudRobotByWxId" parameterType="PcloudRobot">
        update pcloud_robot
        <set>
            <if test="nickName != null">
                nick_name = #{nickName,jdbcType=VARCHAR},
            </if>
            <if test="head != null">
                head = #{head,jdbcType=VARCHAR},
            </if>
            <if test="qrcodeUrl != null">
                qrcode_url = #{qrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="robotType != null">
                robot_type = #{robotType,jdbcType=INTEGER},
            </if>
            <if test="keywordClassifyId != null">
                keyword_classify_id = #{keywordClassifyId,jdbcType=BIGINT},
            </if>
            <if test="state != null">
                state = #{state,jdbcType=INTEGER},
            </if>
            <if test="realStatus != null">
                real_status = #{realStatus,jdbcType=INTEGER},
            </if>
            <if test="wakeUp != null">
                wake_up = #{wakeUp,jdbcType=INTEGER},
            </if>
            <if test="uniqueNumber != null">
                unique_number = #{uniqueNumber, jdbcType=VARCHAR},
            </if>
            <if test="welcomeDuration != null">
                welcome_duration = #{welcomeDuration,jdbcType=INTEGER},
            </if>
        </set>
        where wx_id = #{wxId,jdbcType=VARCHAR}
    </update>

    <update id="updateState" parameterType="PcloudRobot">
        update pcloud_robot set
        state = #{state}
        where id=#{id}
    </update>

    <update id="updateRealStatus" parameterType="map">
        update pcloud_robot set
        real_status = #{realStatus}
        where wx_id=#{wxId}
    </update>

    <select id="getById" parameterType="long" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from pcloud_robot where id=#{id}
    </select>

    <select id="getPcloudRobotList" parameterType="map" resultType="PcloudRobot">
        SELECT
        r.id id,
        r.wx_id wxId,
        r.nick_name nickName,
        r.head head,
        r.qrcode_url qrcodeUrl,
        r.robot_type robotType,
        r.state state,
        r.real_status realStatus,
        r.unique_number uniqueNumber
        FROM
            pcloud_robot r
        WHERE
            1=1
        <if test="nickName!=null">
            and r.nick_name like concat('%', #{nickName}, '%')
        </if>
        <if test="classifyId!=null">
            and r.robot_type = #{classifyId}
        </if>
    </select>

    <select id="getPcloudKeywordRobotCount" parameterType="map" resultType="integer">
        SELECT
        count(1)
        FROM
        pcloud_robot
        WHERE
        1=1
        <if test="nickName!=null">
            and nick_name like concat('%', #{nickName}, '%')
        </if>
    </select>

    <select id="getPcloudKeywordRobotIds" resultType="string">
        select wx_id from pcloud_robot where state = 1 and real_status = 1
    </select>

    <select id="getByRobotWxId" parameterType="string" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from pcloud_robot where wx_id = #{robotWxId}
        limit 1
    </select>

    <select id="getByWxId" parameterType="string" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from pcloud_robot where wx_id = #{wxId}
        limit 1
    </select>

    <select id="getPcloudRobotByType" parameterType="Integer" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from pcloud_robot
        where robot_type = #{robotType}
        and state = 1
        and real_status = 1
        limit 1
    </select>

    <update id="updateWakeUpByClassifyIds" parameterType="list" >
        update pcloud_robot set wake_up = 1 where robot_type IN
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
    </update>

    <update id="clearWakeUp">
        update pcloud_robot set wake_up = 0
    </update>

    <select id="mapByIds4Classify" parameterType="list" resultType="com.pcloud.book.pcloudKeyword.dto.RobotClassifyDTO">
        select
        p.wx_id wxId,
        p.robot_type classifyId,
        p.unique_number uniqueNumber,
        pr.classify_name classifyName
        from
        pcloud_robot p
        left join  pcloud_robot_classify pr
        on p.robot_type = pr.id
        where
        pr.is_delete = 0
        <if test="list != null and list.size > 0">
            and p.wx_id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAllPcloudRobot" resultType="com.pcloud.book.pcloudKeyword.dto.RobotClassifyDTO">
        select
        p.wx_id wxId,
        p.nick_name nickName,
        p.robot_type classifyId,
        p.unique_number uniqueNumber,
        p.state state,
	    p.real_status realStatus,
        pr.classify_name classifyName
        from
        pcloud_robot p
        left join  pcloud_robot_classify pr
        on p.robot_type = pr.id
        where
        pr.is_delete = 0
    </select>

    <select id="getByUniqueNumber" parameterType="string" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from pcloud_robot where unique_number = #{uniqueNumber}
        limit 1
    </select>

    <select id="getPcloudRobotByTypes" parameterType="list" resultType="string">
        select
        DISTINCT wx_id
        from
        pcloud_robot p
        where
        robot_type in
        <foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and state = 1
        and real_status = 1
    </select>

    <select id="getRobotCountByClassifyId" parameterType="long" resultType="int">
        select COUNT(DISTINCT wx_id)
        from pcloud_robot
        where robot_type = #{classifyId}
    </select>

    <update id="updateRobotState" parameterType="map">
        update pcloud_robot set
        state = #{state}
        where id=#{id}
    </update>

    <select id="listSelfRobotByClassify" parameterType="map" resultType="com.pcloud.book.pcloudKeyword.dto.RobotClassifyDTO">
        select
        p.wx_id wxId,
        p.nick_name nickName,
        p.robot_type classifyId,
        p.unique_number uniqueNumber,
        pr.classify_name classifyName
        from
        pcloud_robot p
        left join  pcloud_robot_classify pr
        on p.robot_type = pr.id
        where
        pr.is_delete = 0
        <if test="classifyId != null">
            AND p.robot_type = #{classifyId}
        </if>
        <if test="keyword != null">
            AND (p.wx_id = #{keyword} OR p.unique_number like concat('%', #{keyword}, '%'))
        </if>
        <if test="wxIds != null and wxIds.size > 0">
            AND p.wx_id in
            <foreach collection="wxIds" item="item" open="(" close=")" separator=",">
                '${item}'
            </foreach>
        </if>
    </select>

    <select id="listNoClassifyRobot"  resultMap="BaseResultMap">
        select id, wx_id, unique_number, real_status
        from pcloud_robot where robot_type is null and real_status = 1
    </select>

    <update id="updateRobotType" parameterType="map">
        update pcloud_robot set
        robot_type = #{classifyId}
        where wx_id = #{wxId}
    </update>

    <select id="getPcloudRobotByClassify" resultType="string" parameterType="integer">
        select DISTINCT wx_id
        from pcloud_robot
        where robot_type = #{classifyId}
    </select>

</mapper>