<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.pcloud.book.keywords.dao.impl.BookKeywordDaoImpl">

    <insert id="insert" parameterType="bookKeyword" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_keyword
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_id,
            channel_id,
            book_group_id,
            classify_id,
            set_type,
            rank,
            keyword_id,
            is_warehouse,
            warehouse_id,
            create_user,
            create_time,
            is_edit,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookId,jdbcType=BIGINT},
            #{channelId,jdbcType=BIGINT},
            #{bookGroupId,jdbcType=BIGINT},
            #{classifyId,jdbcType=BIGINT},
            #{setType,jdbcType=TINYINT},
            #{rank,jdbcType=TINYINT},
            #{keywordId,jdbcType=BIGINT},
            #{isWarehouse,jdbcType=TINYINT},
            #{warehouseId,jdbcType=BIGINT},
            #{createUser,jdbcType=BIGINT},
            NOW(),
            #{isEdit,jdbcType=TINYINT},
        </trim>
    </insert>
    
    <insert id="batchInsert" parameterType="bookKeyword" useGeneratedKeys="true" keyProperty="id">
        insert into book_keyword(book_id,
        channel_id,
        book_group_id,
        classify_id,
        set_type,
        rank,
        keyword_id,
        is_warehouse,
        warehouse_id,
        create_user,
        create_time,
        is_edit) values
        <foreach collection="list" item="item" separator=",">
            (#{item.bookId,jdbcType=BIGINT},
            #{item.channelId,jdbcType=BIGINT},
            #{item.bookGroupId,jdbcType=BIGINT},
            #{item.classifyId,jdbcType=BIGINT},
            #{item.setType,jdbcType=TINYINT},
            #{item.rank,jdbcType=TINYINT},
            #{item.keywordId,jdbcType=BIGINT},
            #{item.isWarehouse,jdbcType=TINYINT},
            #{item.warehouseId,jdbcType=BIGINT},
            #{item.createUser,jdbcType=BIGINT},
            NOW(),
            #{item.isEdit,jdbcType=TINYINT})
        </foreach>
    </insert>

    <update id="deleteById" parameterType="map">
        update book_keyword
        <set>
            is_delete = 1,
            update_user = #{updateUser},
            update_time = now()
        </set>
        where keyword_id = #{keywordId,jdbcType=BIGINT}
    </update>

    <update id="setRank" parameterType="bookKeyword">
        update book_keyword
        <set>
            rank = #{rank},
            update_user = #{updateUser},
            update_time = now()
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="getMaxRank" resultType="Integer" parameterType="map">
        select
            IFNULL(MAX(rank), 0)
        from
            book_keyword
        where
            classify_id = #{classifyId}
        <if test="bookGroupId!=null">
            AND book_group_id = #{bookGroupId}
        </if>
          AND is_delete = 0
    </select>

    <select id="listKeywordsByClassify" resultType="ListKeywordVO" parameterType="map">
        SELECT
            bk.id bookKeywordId,
            k.id keywordId,
            k.keywords,
            k.matching_rule matchingRule,
            k.guide,
            bk.rank,
            k.content,
            k.description,
            k.link_url linkUrl,
            k.pic_url picUrl,
            k.reply_type replyType,
            k.serve_id serveId,
            k.serve_type serveType,
            bk.is_warehouse as isWarehouse,
            bk.warehouse_id as warehouseId,
            bk.is_edit as isEdit
        FROM
            book_keyword bk
        JOIN
            keyword k ON bk.keyword_id = k.id
        WHERE
            bk.is_delete = 0
        AND
            k.is_delete = 0
        AND classify_id = #{classifyId}
        <if test="bookGroupId!=null">
            AND book_group_id = #{bookGroupId}
        </if>
        <if test="keywords!=null">
            AND keywords like concat('%', #{keywords}, '%')
        </if>
        order by bk.rank ASC, bk.id DESC
    </select>

    <select id="listFiveKeyword" resultType="keywordDTO" parameterType="map">
        SELECT
        k.keywords,
        k.id keywordId,
        bk.is_warehouse as isWarehouse,
        bk.warehouse_id as warehouseId,
        k.guide
        FROM
        book_keyword bk
        JOIN
        keyword k ON bk.keyword_id = k.id
        WHERE
        bk.is_delete = 0
        AND
        k.is_delete = 0
        AND classify_id = #{classifyId}
        AND book_group_id = #{bookGroupId} order by bk.rank, bk.id desc limit 10
    </select>

    <select id="getKeywordId" resultType="ReplyKeywordDTO" parameterType="map">
        SELECT
            k.id keywordId,
            k.keywords,
            k.reply_type replyType,
            k.content,
            k.description,
            k.link_url linkUrl,
            k.pic_url picUrl,
            bk.is_warehouse as isWarehouse,
            bk.warehouse_id as warehouseId,
            k.reply_type replyType
        FROM
            book_keyword bk
        JOIN
            keyword k ON bk.keyword_id = k.id
        WHERE
            bk.is_delete = 0 AND k.is_delete = 0
            AND classify_id in (0, ${classifyId})
            AND book_group_id = #{bookGroupId}
            AND ((k.keywords = #{content} and matching_rule = 1 ) or (k.keywords like
            concat('%',#{content},'%') and matching_rule = 0))
            order by set_type desc, matching_rule desc, bk.id desc limit 1
    </select>

    <select id="checkKeyword" resultType="Boolean" parameterType="map">
        SELECT
            count(1)
        FROM
            book_keyword bk
        JOIN keyword k ON bk.keyword_id = k.id
        WHERE
            classify_id = #{classifyId}
        AND book_group_id = #{bookGroupId}
        <if test="keywordId != null">
            AND keyword_id != #{keywordId}
        </if>
        AND keywords = #{keywords}
        AND bk.is_delete = 0
        AND k.is_delete = 0
    </select>

    <update id="updateIsWarehouse" parameterType="long" >
        update book_keyword set is_warehouse = 1, warehouse_id = #{warehouseId} where id = #{bookKeywordId}
    </update>

    <select id="getByBookKeyword" resultType="KeywordVO" parameterType="Long">
        SELECT
            k.id keywordId,
            bk.id bookKeywordId,
            bk.classify_id classifyId,
            bk.book_group_id bookGroupId,
            keywords,
            matching_rule matchingRule,
            guide,
            content,
            description,
            link_url linkUrl,
            pic_url picUrl,
            reply_type replyType,
            serve_id serveId,
            bk.is_warehouse as isWarehouse,
            bk.warehouse_id as warehouseId,
            serve_type serveType
        FROM
            book_keyword bk
        JOIN
            keyword k
        ON
            bk.keyword_id = k.id
        WHERE
          bk.id = #{_parameter} AND bk.is_delete = 0 AND k.is_delete = 0 limit 1
    </select>


    <select id="getKeywordCount" resultType="Integer" parameterType="map">
        SELECT
          count(1)
        FROM
        book_keyword bk
        JOIN
        keyword k ON bk.keyword_id = k.id
        WHERE
        bk.is_delete = 0
        AND
        k.is_delete = 0
        <if test="classifyId != null">
            AND classify_id = #{classifyId}
        </if>
        AND book_group_id = #{bookGroupId}
    </select>

    <!--根据社群书id获取关键词统计-->
    <select id="getKeywordInfoByBookGroupId" parameterType="map" resultType="com.pcloud.book.keywords.dto.KeywordStatisticsDTO">
        SELECT
        t.keyword_id as keywordId,
        t.create_time as keywordCreateDate,
        t.is_warehouse as isWarehouse,
        t.warehouse_id as warehouseId,
        t2.keywords as keyword
        FROM
        book_keyword t
        INNER JOIN keyword t2 on t.keyword_id=t2.id
        WHERE
        t.is_delete = 0
        AND t.book_group_id = #{bookGroupId}
        <if test="classifyId!=null">
            AND t.classify_id=#{classifyId}
        </if>
        GROUP BY t.keyword_id
        order by t.create_time desc
    </select>

    <!--根据分类id集合获取-->
    <select id="getKeywordsByClassifyIds" parameterType="map" resultType="com.pcloud.book.keywords.vo.ListKeywordVO">
        SELECT
        bk.id bookKeywordId,
        k.id keywordId,
        k.keywords,
        k.matching_rule matchingRule,
        k.guide,
        bk.rank,
        k.content,
        k.description,
        k.link_url linkUrl,
        k.pic_url picUrl,
        k.reply_type replyType,
        k.serve_id serveId,
        k.serve_type serveType,
        bk.classify_id classifyId
        FROM
        book_keyword bk
        JOIN keyword k ON bk.keyword_id = k.id
        WHERE
        bk.is_delete = 0
        AND k.is_delete = 0
        AND (bk.classify_id IN
        <foreach collection="classifyIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        or bk.book_id =#{bookId})
        ORDER BY
        bk.rank ASC,bk.id DESC
    </select>

    <select id="getKeywordsIdsByBookGroupId" parameterType="map" resultType="com.pcloud.book.group.vo.SyncKeyworsVO">
        select k.id keywordsId, bk.is_warehouse isWarehouse, bk.warehouse_id warehouseId from book_keyword bk
        JOIN keyword k ON bk.keyword_id = k.id where bk.book_group_id = #{bookGroupId} and bk.classify_id = #{classifyId}
        and k.is_delete = 0 and bk.is_delete = 0
        group by k.id
    </select>

    <update id="deleteKeywords" parameterType="map">
        update book_keyword
        <set>
            is_delete = 1,
            update_user = #{partyId},
            update_time = now()
        </set>
        where keyword_id = #{keywordId,jdbcType=BIGINT}
        and book_group_id = #{bookGroupId,jdbcType=BIGINT}
        and classify_id = #{classifyId,jdbcType=BIGINT}
    </update>
    
    <select id="getServiceByWeixinGroup" parameterType="map" resultType="com.pcloud.book.keywords.dto.ServiceResourceDTO">
        SELECT
        t.channel_id as channelId,
        t2.pic_url as coverImg,
        t2.serve_id as fromId,
        t2.serve_type as typeCode,
        t2.link_url as url,
        t2.keywords as keyword
        FROM
        book_keyword t
        INNER JOIN keyword t2 on t.keyword_id=t2.id
        WHERE
        t.is_delete = 0
        AND t2.is_delete = 0
        AND t.book_group_id = #{bookGroupId}
        AND t2.reply_type = 4
        order by t.create_time desc
    </select>
</mapper>