<?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.group.dao.impl.BookGroupServeDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.BookGroupServe">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="serve_id" property="serveId" jdbcType="BIGINT"/>
        <result column="serve_type" property="serveType" jdbcType="VARCHAR"/>
        <result column="serve_url" property="serveUrl" jdbcType="VARCHAR"/>
        <result column="short_url" property="shortUrl" jdbcType="VARCHAR"/>
        <result column="book_group_id" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="type_code" property="typeCode" jdbcType="VARCHAR"/>
    </resultMap>


    <sql id="Base_Column_List">
        id, serve_id, serve_type, serve_url, short_url, book_group_id, create_user, create_time, type_code
    </sql>

    <insert id="insert" parameterType="com.pcloud.book.group.entity.BookGroupServe" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_group_serve
        <trim prefix="(" suffix=")" suffixOverrides=",">
            serve_id,
            serve_type,
            serve_url,
            short_url,
            book_group_id,
            create_user,
            create_time,
            type_code
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{serveId,jdbcType=BIGINT},
            #{serveType,jdbcType=VARCHAR},
            #{serveUrl,jdbcType=VARCHAR},
            #{shortUrl,jdbcType=VARCHAR},
            #{bookGroupId,jdbcType=BIGINT},
            #{createUser,jdbcType=INTEGER},
            NOW(),
            #{typeCode}
        </trim>
    </insert>

    <!--批量插入-->
    <insert id="batchInsert"  parameterType="com.pcloud.book.group.entity.BookGroupServe" useGeneratedKeys="true" keyProperty="id">
        insert into book_group_serve (
        serve_id,
        serve_type,
        serve_url,
        short_url,
        book_group_id,
        create_user,
        create_time,
        type_code
        ) values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.serveId,jdbcType=BIGINT},
            #{item.serveType,jdbcType=VARCHAR},
            #{item.serveUrl,jdbcType=VARCHAR},
            #{item.shortUrl,jdbcType=VARCHAR},
            #{item.bookGroupId,jdbcType=BIGINT},
            #{item.createUser,jdbcType=INTEGER},
            NOW(),
            #{item.typeCode}
            )
        </foreach>
    </insert>

    <!--删除-->
    <delete id="deleteById" parameterType="long">
        delete from book_group_serve
        where id=#{id}
    </delete>

    <!--根据社群书id获取集合-->
    <select id="getListByBookGroupId" parameterType="map" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group_serve
        where book_group_id=#{bookGroupId}
    </select>

    <!--根据bookGroupId删除-->
    <delete id="deleteByBookGroupId" parameterType="long" >
        delete from book_group_serve
        where book_group_id=#{bookGroupId}
    </delete>

    <!--根据类型查询集合-->
    <select id="getListByServerType" parameterType="String" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group_serve
        where serve_type=#{serveType}
    </select>

    <!--根据id更新短链-->
    <update id="updateShortUrl" parameterType="map">
        update book_group_serve set
        short_url=#{shortUrl}
        where id=#{id}
    </update>

    <!--查询所有作品-->
    <select id="getProductServeList" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group_serve
        where serve_type='PRODUCT'
    </select>

    <!--查询所有-->
    <select id="getAllAppServe" resultType="com.pcloud.book.group.entity.BookGroupApp">
        SELECT
        t.book_group_id bookGroupId,
        t.serve_id serveId,
        t.serve_type serveType,
        t.create_user createUser,
        0 AS classifyId,
        4 AS originType
        FROM
        book_group_serve t
        GROUP BY
        t.book_group_id,
        t.serve_id,
        t.serve_type
    </select>

    <!--根据社群码id查询资源服务数量-->
    <select id="getCountByBookGroupId" parameterType="map" resultType="integer">
        select count(1)
        from book_group_serve
        where book_group_id=#{bookGroupId}
    </select>

    <!--根据社群书id获取资源链接和名称-->
    <select id="getListByBookGroupIds" parameterType="list" resultType="com.pcloud.book.group.entity.BookGroupServe">
        select t.serve_id serveId,
        t.book_group_id bookGroupId,
        t.serve_type serveType,
        t.serve_url serveUrl
        from book_group_serve t
        where book_group_id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <!--根据社群码id集合删除-->
    <delete id="deleteByBookGroupIds" parameterType="map" >
        delete from book_group_serve
        where book_group_id in
        <foreach collection="bookGroupIds" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </delete>

    <select id="getCountByServeIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupCountDTO">
        select serve_id serveId, serve_type serveType, create_user adviserId, count(book_group_id) bookGroupCount
        from book_group_serve
        where serve_id in
        <foreach collection="serveIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and serve_type = #{serveType}
        and create_user = #{adviserId}
        group by serve_id
    </select>

    <select id="getTypeCodeNull" parameterType="string" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM book_group_serve
        WHERE (type_code IS NULL OR type_code ="")
        AND serve_type = #{serveType}
    </select>

    <update id="batchUpdate" parameterType="list">
        update book_group_serve
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="type_code =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.typeCode!=null">
                        when id=#{item.id} then #{item.typeCode}
                    </if>
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="item">
                id = #{item.id}
            </foreach>
        </where>
    </update>

    <select id="getServeList4Applet" parameterType="map" resultType="com.pcloud.book.group.vo.BookGroupServeCountVO">
        SELECT
        g.book_id bookId,
        g.create_user adviserId,
        g.channel_id channelId,
        COUNT(1) serveCount
        FROM book_group_serve s LEFT JOIN book_group g ON s.book_group_id =g.id AND g.is_delete=0
        WHERE 1=1
        <if test="bookIds != null and bookIds.size>0">
            and g.book_id IN
            <foreach collection="bookIds" item="bookId" separator="," open="(" close=")">
                ${bookId}
            </foreach>
        </if>
        AND s.type_code NOT in('INTERACT','MEMBER','ENROLL','QA','TOOL','TUTORIAL','ATTENDANCE_TASK','THIRDPARTY','LINK')
        GROUP BY g.book_id,g.create_user,g.channel_id
    </select>

    <!--客服机器人、1v1、小睿的资源数-->
    <select id="mapBookGroupServeCount" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
            CONCAT(bg.book_id,'_',bg.channel_id,'_',bg.create_user) bookChannelAdviserId, COUNT(bgs.id) serveCount,
            bg.book_id bookId, bg.create_user adviserId, bg.channel_id channelId,COUNT(DISTINCT bg.group_qrcode_url) qrcodeCount,
            bg.join_group_type joinGroupType
        FROM `book_group` bg LEFT JOIN book_group_serve bgs ON bg.id = bgs.book_group_id
        WHERE
            bg.is_delete=0
            AND bg.book_id IN
            <foreach collection="bookIds" item="bookId" separator="," open="(" close=")">
                ${bookId}
            </foreach>
            AND bg.channel_id IN
            <foreach collection="channelIds" item="channelId" separator="," open="(" close=")">
                ${channelId}
            </foreach>
            AND bg.create_user IN
            <foreach collection="adviserIds" item="adviserId" separator="," open="(" close=")">
                ${adviserId}
            </foreach>
            AND bg.join_group_type IN (2,3,4)
        GROUP BY bg.book_id,bg.create_user,bg.channel_id
    </select>

    <!--社群资源数-->
    <select id="mapBookGroupQrcodeServeCount" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
            CONCAT(bg.book_id,'_',bg.channel_id,'_',bg.create_user) bookChannelAdviserId,COUNT(DISTINCT k.id) serveCount,
            bg.book_id bookId,bg.create_user adviserId,bg.channel_id channelId,COUNT(DISTINCT bg.group_qrcode_url) qrcodeCount
        FROM
            book_group bg
            LEFT JOIN book_keyword bk ON bg.id=bk.book_group_id AND bk.is_delete = 0
            LEFT JOIN keyword k ON bk.keyword_id = k.id AND k.is_delete = 0
        WHERE
            bg.is_delete = 0
            AND bg.join_group_type = #{joinGroupType}
            AND bg.book_id IN
            <foreach collection="bookIds" item="bookId" separator="," open="(" close=")">
                ${bookId}
            </foreach>
            AND bg.channel_id IN
            <foreach collection="channelIds" item="channelId" separator="," open="(" close=")">
                ${channelId}
            </foreach>
            AND bg.create_user IN
            <foreach collection="adviserIds" item="adviserId" separator="," open="(" close=")">
                ${adviserId}
            </foreach>
        GROUP BY bg.book_id,bg.create_user,bg.channel_id
    </select>
</mapper>
