<?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.BookGroupDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.BookGroup">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="group_qrcode_url" property="groupQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="group_qrcode_name" property="groupQrcodeName" jdbcType="VARCHAR"/>
        <result column="pro_label_id" property="proLabelId" jdbcType="BIGINT"/>
        <result column="dep_label_id" property="depLabelId" jdbcType="BIGINT"/>
        <result column="pur_label_id" property="purLabelId" jdbcType="BIGINT"/>
        <result column="join_title" property="joinTitle" jdbcType="VARCHAR"/>
        <result column="join_slogan" property="joinSlogan" jdbcType="VARCHAR"/>
        <result column="personal_qrcode_url" property="personalQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="scene_id" property="sceneId" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="BIT"/>
        <result column="is_show_book_name" property="isShowBookName" jdbcType="BOOLEAN"/>
        <result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
        <result column="add_friend_guide" property="addFriendGuide" jdbcType="VARCHAR"/>
        <result column="customer_service_name" property="customerServiceName" jdbcType="VARCHAR"/>
        <result column="is_invite_group" property="isInviteGroup" jdbcType="BOOLEAN"/>
        <result column="book_group_cipher" property="bookGroupCipher" jdbcType="VARCHAR"/>
        <result column="last_push_update_time" property="lastPushUpdateTime" jdbcType="TIMESTAMP"/>
        <result column="is_some_update" property="isSomeUpdate" jdbcType="BOOLEAN"/>
        <result column="agent_id" property="agentId" jdbcType="BIGINT"/>
        <result column="belong_special_agent" property="belongSpecialAgent" jdbcType="BOOLEAN"/>
        <result column="short_book_name" property="shortBookName" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap id="BookGroupDTO" type="com.pcloud.book.group.dto.BookGroupDTO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="group_qrcode_url" property="groupQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="group_qrcode_name" property="groupQrcodeName" jdbcType="VARCHAR"/>
        <result column="pro_label_id" property="proLabelId" jdbcType="BIGINT"/>
        <result column="dep_label_id" property="depLabelId" jdbcType="BIGINT"/>
        <result column="pur_label_id" property="purLabelId" jdbcType="BIGINT"/>
        <result column="join_title" property="joinTitle" jdbcType="VARCHAR"/>
        <result column="join_slogan" property="joinSlogan" jdbcType="VARCHAR"/>
        <result column="personal_qrcode_url" property="personalQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="scene_id" property="sceneId" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="BIT"/>
        <result column="book_img" property="bookImg" jdbcType="VARCHAR"/>
        <result column="isbn" property="isbn" jdbcType="VARCHAR"/>
        <result column="BOOK_NAME" property="bookName" jdbcType="VARCHAR"/>
        <result column="book_clock_info_id" property="bookClockInfoId" jdbcType="BIGINT"/>
        <result column="is_show_book_name" property="isShowBookName" jdbcType="BOOLEAN"/>
        <result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
        <result column="add_friend_guide" property="addFriendGuide" jdbcType="VARCHAR"/>
        <result column="customer_service_name" property="customerServiceName" jdbcType="VARCHAR"/>
        <result column="is_invite_group" property="isInviteGroup" jdbcType="BOOLEAN"/>
        <result column="book_group_cipher" property="bookGroupCipher" jdbcType="VARCHAR"/>
        <result column="last_push_update_time" property="lastPushUpdateTime" jdbcType="TIMESTAMP"/>
        <result column="is_some_update" property="isSomeUpdate" jdbcType="BOOLEAN"/>
        <result column="agent_id" property="agentId" jdbcType="BIGINT"/>
        <result column="belong_special_agent" property="belongSpecialAgent" jdbcType="BOOLEAN"/>
        <result column="short_book_name" property="shortBookName" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, book_id, channel_id, scene_id, group_qrcode_url, group_qrcode_name, pro_label_id, dep_label_id,
        pur_label_id, join_title, join_slogan, personal_qrcode_url, product_id, create_user,
        create_time,
        update_time, is_delete, is_show_book_name,join_group_type,add_friend_guide,customer_service_name,
        is_invite_group,book_group_cipher, last_push_update_time, is_some_update, agent_id, belong_special_agent,
        short_book_name
    </sql>

    <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id = #{id,jdbcType=BIGINT}
    </select>

    <select id="getDTOById" resultMap="BookGroupDTO" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id = #{id,jdbcType=BIGINT}
    </select>

    <select id="getBookInfo" resultMap="BookGroupDTO" parameterType="java.lang.Long">
        select book_name,cover_img as book_img
        from book_group b LEFT JOIN book a on b.book_id = a.book_id
        where b.id = #{id,jdbcType=BIGINT}
    </select>


    <select id="getDTOByIds" resultMap="BookGroupDTO" parameterType="list">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="getDTOByBookId" resultMap="BookGroupDTO" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where is_delete = 0
        and book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        and create_user = #{adviserId,jdbcType=BIGINT}
        ORDER BY create_time ASC limit 1
    </select>

    <select id="getDTOByBookIdsAnsAdviserIds" resultMap="BookGroupDTO" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where is_delete = 0
        and book_id in
        <foreach collection="bookIds" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
        and create_user in
        <foreach collection="adviserIds" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="getBookGroupCount" resultType="Integer" parameterType="Long">
        SELECT
            COUNT(DISTINCT G.id)
        FROM
            book_group G
        LEFT JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID
        AND G.CHANNEL_ID = A.CHANNEL_ID
        AND G.CREATE_USER = A.ADVISER_ID
        LEFT JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
        WHERE
            G.create_user = #{adviserId, jdbcType=BIGINT}
        AND G.is_delete = 0
        AND (
            A.BOOK_ADVISER_ID IS NULL
            OR A.IS_DELETE = 0
        )
        AND (
            B.BOOK_ID IS NULL
            OR B.IS_DELETE = 0
        )
    </select>

    <delete id="deleteById" parameterType="java.lang.Long">
        update book_group set is_delete = 1
        where id = #{id,jdbcType=BIGINT} and is_delete = 0
    </delete>

    <update id="deleteByBookId" parameterType="map">
        update book_group set is_delete = 1
        where book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        <if test="adviserId != null">
            and create_user = #{adviserId,jdbcType=BIGINT}
        </if>
        and is_delete = 0
    </update>
    
    <update id="recoverByBookId" parameterType="map">
        update book_group set is_delete = 0
        where book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        <if test="adviserId != null">
            and create_user = #{adviserId,jdbcType=BIGINT}
        </if>
        and is_delete = 1
    </update>
    
    <update id="linkBookGroup" parameterType="com.pcloud.book.group.entity.BookGroup">
        update book_group set book_id = #{bookId,jdbcType=BIGINT}
        where id = #{id,jdbcType=BIGINT} and is_delete = 0
    </update>

    <insert id="insert" parameterType="com.pcloud.book.group.entity.BookGroup" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_group
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_id,
            channel_id,
            scene_id,
            group_qrcode_url,
            group_qrcode_name,
            pro_label_id,
            dep_label_id,
            pur_label_id,
            join_title,
            join_slogan,
            personal_qrcode_url,
            product_id,
            create_user,
            create_time,
            update_time,
            is_show_book_name,
            add_friend_guide,
            customer_service_name,
            is_invite_group,
            book_group_cipher,
            last_push_update_time,
            is_some_update,
            agent_id,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookId,jdbcType=BIGINT},
            #{channelId,jdbcType=BIGINT},
            #{sceneId,jdbcType=BIGINT},
            #{groupQrcodeUrl,jdbcType=VARCHAR},
            #{groupQrcodeName,jdbcType=VARCHAR},
            #{proLabelId,jdbcType=BIGINT},
            #{depLabelId,jdbcType=BIGINT},
            #{purLabelId,jdbcType=BIGINT},
            #{joinTitle,jdbcType=VARCHAR},
            #{joinSlogan,jdbcType=VARCHAR},
            #{personalQrcodeUrl,jdbcType=VARCHAR},
            #{productId,jdbcType=BIGINT},
            #{createUser,jdbcType=BIGINT},
            NOW(),
            NOW(),
            #{isShowBookName,jdbcType=BOOLEAN},
            #{addFriendGuide,jdbcType=VARCHAR},
            #{customerServiceName,jdbcType=VARCHAR},
            #{isInviteGroup,jdbcType=BOOLEAN},
            #{bookGroupCipher,jdbcType=VARCHAR},
            #{lastPushUpdateTime,jdbcType=TIMESTAMP},
            #{isSomeUpdate,jdbcType=BOOLEAN},
            #{agentId,jdbcType=BIGINT},
        </trim>
    </insert>

    <update id="update" parameterType="com.pcloud.book.group.entity.BookGroup">
        update book_group
        <set>
            <if test="bookId != null">
                book_id = #{bookId,jdbcType=BIGINT},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId,jdbcType=BIGINT},
            </if>
            <if test="groupQrcodeUrl != null">
                group_qrcode_url = #{groupQrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="groupQrcodeName != null">
                group_qrcode_name = #{groupQrcodeName,jdbcType=VARCHAR},
            </if>
            <if test="proLabelId != null">
                pro_label_id = #{proLabelId,jdbcType=BIGINT},
            </if>
            <if test="depLabelId != null">
                dep_label_id = #{depLabelId,jdbcType=BIGINT},
            </if>
            <if test="purLabelId != null">
                pur_label_id = #{purLabelId,jdbcType=BIGINT},
            </if>
            <if test="joinTitle != null">
                join_title = #{joinTitle,jdbcType=VARCHAR},
            </if>
            <if test="joinSlogan != null">
                join_slogan = #{joinSlogan,jdbcType=VARCHAR},
            </if>
            <if test="personalQrcodeUrl != null">
                personal_qrcode_url = #{personalQrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="productId != null">
                product_id = #{productId,jdbcType=BIGINT},
            </if>
            <if test="sceneId != null and sceneId == 0">
                scene_id = null,
            </if>
            <if test="sceneId != null and sceneId != 0">
                scene_id = #{sceneId},
            </if>
            <if test="joinGroupType != null">
                join_group_type = #{joinGroupType},
            </if>
            <if test="addFriendGuide != null">
                add_friend_guide = #{addFriendGuide},
            </if>
            <if test="customerServiceName != null">
                customer_service_name = #{customerServiceName},
            </if>
            <if test="isInviteGroup != null">
                is_invite_group = #{isInviteGroup},
            </if>
            <if test="bookGroupCipher != null">
                book_group_cipher = #{bookGroupCipher},
            </if>
            update_time = NOW(),
            <if test="lastPushUpdateTime != null">
                last_push_update_time = #{lastPushUpdateTime},
            </if>
            <if test="isSomeUpdate != null">
                is_some_update = #{isSomeUpdate},
            </if>
            <if test="agentId != null">
                agent_id = #{agentId},
            </if>
            <if test="shortBookName != null">
                short_book_name = #{shortBookName},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="getQrcodeNameAndProId" resultType="QrcodeNameAndProIdDTO" parameterType="java.lang.Long">
        select
        group_qrcode_name groupQrcodeName,
        join_group_type joinGroupType,
        product_id productId
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>

    <select id="getRankType" resultType="Integer" parameterType="java.lang.Long">
        select
        rank_type
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>

    <update id="updateRankType" parameterType="map">
        update book_group
        <set>
            rank_type = #{rankType},
            update_time = NOW()
        </set>
        where id = #{bookGroupId,jdbcType=BIGINT}
    </update>

    <select id="getSpareQr" resultType="String" parameterType="java.lang.Long">
        select
        personal_qrcode_url
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>


    <!--获取社群码基本信息(包括书籍信息)-->
    <select id="getBookBaseInfoByIds" parameterType="list" resultMap="BookGroupDTO">
        SELECT
        bg.id,
        bg.book_id,
        b.book_name,
        bg.group_qrcode_name,
        b.COVER_IMG book_img,
        b.ISBN,
        b.BOOK_NAME
        FROM
        book_group bg
        LEFT JOIN book b ON bg.book_id = b.BOOK_ID
        where id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="getBaseInfoBySceneId" resultMap="BookGroupDTO" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where scene_id = #{_parameter} and is_delete = 0
    </select>

    <select id="isSpecialAgent" resultType="Boolean" parameterType="Long">
        select
        count(1)
        from book_special_agent
        where agent_id = #{_parameter}
    </select>

    <update id="updatePersonQrcode" parameterType="map">
        update book_group set personal_qrcode_url = #{newQrcodeUrl} where personal_qrcode_url = #{oldQrcodeUrl}
    </update>

    <!--获取社群码基本信息(包括书籍信息,及BookClockInfoId)-->
    <select id="getBookInfoByIdsWithBookClockInfoId" parameterType="map" resultMap="BookGroupDTO">
        SELECT
        bg.id,
        bg.book_id,
        bg.group_qrcode_name,
        b.COVER_IMG book_img,
        b.ISBN,
        b.BOOK_NAME,
        #{bookClockInfoId} book_clock_info_id
        FROM
        book_group bg
        LEFT JOIN book b ON bg.book_id = b.BOOK_ID
        where id in
        <foreach collection="bookGroupIdList" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <!--更新是否显示书名-->
    <update id="updateIsShowBookName" parameterType="map">
        update book_group set
        is_show_book_name=#{isShowBookName}
        where create_user=#{partyId}
    </update>

    <!--获取是否显示书名-->
    <select id="getIsShowBookName" parameterType="Long" resultType="Boolean">
        select IFNULL(is_show_book_name,0) from book_group
        where create_user=#{partyId} limit 1
    </select>

    <!--删除-->
    <update id="deleteByBookGroupId" parameterType="map">
        update book_group
        set is_delete = 1,
        update_time=now()
        where id = #{bookGroupId} and is_delete = 0
    </update>

    <select id="listPageBook4AdBrand" parameterType="map" resultType="com.pcloud.book.advertising.dto.Book4AdvertisingTagDTO">
        SELECT
            b.BOOK_ID bookId,
            b.ISBN isbn,
            b.BOOK_NAME bookName,
            g.create_time createTime
        FROM
            book_group g
        LEFT JOIN book b ON g.book_id = b.BOOK_ID
        WHERE
            g.is_delete = 0
            AND b.IS_DELETE = 0
            AND g.book_id != 0
        <if test="name != null">
            AND (
            b.BOOK_NAME LIKE CONCAT('%', #{name},'%')
            OR b.ISBN LIKE  CONCAT('%', #{name},'%')
            )
        </if>
        GROUP BY
            g.book_id
        ORDER BY
            g.create_time DESC, g.id DESC
    </select>

    <!--根据id获取社群码基本信息(包括书籍信息)-->
    <select id="getBookBaseInfoById" parameterType="list" resultMap="BookGroupDTO">
        SELECT
        bg.id,
        bg.book_id,
        bg.group_qrcode_name,
        bg.add_friend_guide,
        bg.is_invite_group,
        b.book_name,
        b.cover_img book_img,
        b.isbn
        FROM
        book_group bg
        LEFT JOIN book b ON bg.book_id = b.BOOK_ID
        where bg.id=#{bookGroupId}
    </select>

    <select id="getBookGroupFriendsCountByAdviser" resultType="integer" parameterType="long">
        SELECT
        COUNT(DISTINCT c.wx_user_id)
        FROM
        book_group G
        LEFT JOIN book_group_cipher_user c ON G.id = c.book_group_id
        WHERE
        G.create_user = #{adviserId, jdbcType=BIGINT}
        AND G.is_delete = 0
    </select>

    <!--根据社群码暗号获取-->
    <select id="getByBookGroupCipher" parameterType="String" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group
        where book_group_cipher=#{bookGroupCipher}
        and is_delete=0
        limit 1
    </select>

    <!--根据入群方式查询社群书集合-->
    <select id="getBookGroupByJoinGroupType" parameterType="Integer" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group
        where join_group_type=#{joinGroupType}
        and is_delete=0
    </select>

    <!--获取1V1的没有暗号的社群书-->
    <select id="getNoCipherSelfBookGroups" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group
        where join_group_type=2
        and book_group_cipher is null
    </select>

    <!--更新暗号-->
    <update id="updateCipher" parameterType="map" >
        update book_group set
        book_group_cipher = #{bookGroupCipher},
        update_time=now()
        where id=#{id}
    </update>

    <!--更新入群方式-->
    <update id="updateJoinGroupType" parameterType="map">
        update book_group set
        join_group_type = #{joinGroupType},
        update_time=now()
        where id=#{id}
    </update>

    <resultMap id="BookGroupAnalysisVO" type="com.pcloud.book.group.vo.BookGroupAnalysisVO">
        <id column="BOOK_GROUP_ID" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="group_qrcode_url" property="groupQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="BOOK_ID" property="bookId" jdbcType="BIGINT"/>
        <result column="ISBN" property="isbn" jdbcType="VARCHAR" />
        <result column="BOOK_NAME" property="bookName" jdbcType="VARCHAR" />
        <result column="TEMPLET_ID" property="templetId" jdbcType="BIGINT" />
        <result column="SECOND_TEMPLET_ID" property="secondTempletId" jdbcType="BIGINT" />
        <result column="ADVISER_ID" property="adviserId" jdbcType="BIGINT" />
        <result column="CHANNEL_ID" property="channelId" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="is_fund_support" property="isFundSupport" jdbcType="BIT" />
        <result column="is_copyright" property="isCopyright" jdbcType="BIT" />
        <result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
    </resultMap>
    <select id="listPageBookGroupAnalysis" parameterType="map" resultMap="BookGroupAnalysisVO">
        SELECT
        G.id BOOK_GROUP_ID,
        G.group_qrcode_url,
        A.BOOK_ID,
        B.ISBN,
        B.BOOK_NAME,
        A.TEMPLET_ID,
        A.SECOND_TEMPLET_ID,
        G.channel_id CHANNEL_ID,
        G.create_user ADVISER_ID,
        G.create_time,
        IF (ISNULL(BF.BOOK_FUND_ID), 0, 1) is_fund_support,
        IF (ISNULL(i.id), 0, 1) is_copyright,
        G.join_group_type
        FROM
        book_group G
        LEFT JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID AND G.CHANNEL_ID = A.CHANNEL_ID AND G.CREATE_USER = A.ADVISER_ID
        LEFT JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
        LEFT JOIN BOOK_FUND BF ON BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW() AND BF.START_TIME <![CDATA[ < ]]> NOW()
        LEFT JOIN book_auth_info i ON G.book_id = i.book_id AND i.book_status = 1 AND i.is_group_book = 1
        WHERE  1 = 1
        AND G.is_delete = 0
        AND A.BOOK_ID IS NOT NULL
        AND (
        A.BOOK_ADVISER_ID IS NULL
        OR A.IS_DELETE = 0
        )
        AND (
        B.BOOK_ID IS NULL
        OR B.IS_DELETE = 0
        )
        <if test="isFundSupport != null and isFundSupport == 1">
            AND BF.BOOK_FUND_ID IS NOT NULL
        </if>
        <if test="isFundSupport != null and isFundSupport == 0">
            AND BF.BOOK_FUND_ID IS NULL
        </if>
        <if test="isCopyright != null and isCopyright == 1">
            AND i.id IS NOT NULL
        </if>
        <if test="isCopyright != null and isCopyright == 0">
            AND i.id IS NULL
        </if>
        <if test="bookIds != null">
            AND G.book_id IN
            <foreach collection="bookIds" index="i" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        <if test="adviserIds != null">
            AND G.create_user IN
            <foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        <if test="agentIds != null">
            AND G.agent_id IN
            <foreach collection="agentIds" index="i" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        GROUP BY
        G.id
        ORDER BY
        G.create_time DESC,
        G.id DESC
    </select>

    <!--更新是否邀请入群-->
    <update id="updateIsInviteGroup" parameterType="map">
        update book_group set
        is_invite_group=#{isInviteGroup},
        update_time=now()
        where id=#{id}
    </update>

    <!--更新1v1是否有更新-->
    <update id="updateIsSomeUpdate" parameterType="map">
        update book_group set
        is_some_update=#{isSomeUpdate},
        update_time=now()
        where id=#{id}
    </update>

    <!--更新上次推送时间-->
    <update id="updateLastPushUpdateTime" parameterType="map">
        update book_group set
        last_push_update_time=now(),
        update_time=now()
        where id=#{bookGroupId}
    </update>

    <!--根据类型获取社群书数量-->
    <select id="getBookGroupCountByJoinGroupType" parameterType="map" resultType="Integer">
        select count(1) from book_group
        where is_delete=0
        <if test="joinGroupType!=null">
            and join_group_type=#{joinGroupType}
        </if>
    </select>

    <!--查询agentId为空的数据-->
    <select id="getEmptyAgentIdAdviserId" resultType="Long">
        select distinct t.create_user from book_group t where t.agent_id is NULL
    </select>

    <!--更新出版社id-->
    <update id="updateAgentIdByAdviserId" parameterType="map">
        update book_group set
        agent_id=#{agentId}
        where create_user=#{adviserId}
    </update>

    <!--获取出版社创建社群书数量排行-->
    <select id="getTopAgentCreateBookGroup" parameterType="map" resultType="com.pcloud.book.group.dto.TopAgentBookGroupDTO">
        SELECT
        t.agent_id agentId,
        COUNT(1) count
        FROM
        book_group t
        WHERE
        t.is_delete=0
        <if test="joinGroupType!=null">
            AND t.join_group_type = #{joinGroupType}
        </if>
        <if test="agentIds!=null and agentIds.size()>0">
            AND agent_id IN
            <foreach collection="agentIds" open="(" separator="," close=")" item="item" index="index">
                #{item}
            </foreach>
        </if>
        GROUP BY
        t.agent_id
        HAVING agent_id is not null
        ORDER BY count DESC
        <if test="top!=null">
            LIMIT #{top}
        </if>
    </select>

    <!--获取所有已建分类的1v1的社群码-->
    <select id="get1v1HasClassifyBookGroupIds" resultType="Long">
        SELECT DISTINCT
        t.id
        FROM
        book_group t
        INNER JOIN book_group_classify t1 ON t.id = t1.book_group_id
        WHERE
        t.join_group_type = 2;
    </select>

    <!--获取出版社统计信息-->
    <select id="getAgentStatisticsInfo"  parameterType="map" resultType="agentStatisticsInfoDTO">
        SELECT
            agent_id AS agentId,
            COUNT(DISTINCT id) groupBookCount
        FROM
            book_group
        WHERE
            join_group_type = 2
            AND is_delete = 0
        <if test="agentId != null">
            AND agent_id = #{agentId}
        </if>
        <if test="specialAgentIds !=null and specialAgentIds.size()>0">
            AND agent_id IN
            <foreach collection="specialAgentIds" open="(" separator="," close=")" item="item" index="index">
                ${item}
            </foreach>
        </if>
        GROUP BY agent_id
        HAVING agent_id IS NOT NULL
        ORDER BY groupBookCount DESC
    </select>

    <!--获取出版社关联小号-->
    <select id="getRobotWxidsByAgent" parameterType="long" resultType="string">
        SELECT
            DISTINCT u.alt_id
        FROM
            book_group bg
        LEFT JOIN book_group_cipher_user u ON bg.id = u.book_group_id
        WHERE
            bg.join_group_type = 2
            AND bg.is_delete = 0
            AND u.alt_id is not null
            AND bg.agent_id = #{agentId}
    </select>

    <!--个人号社群书统计-->
    <select id="getSelfBookGroupStatistics" parameterType="map" resultType="com.pcloud.book.group.dto.SelfBookGroupStDTO">
        SELECT
        t.book_id bookId,
        t1.BOOK_NAME bookName,
        t1.ISBN isbn,
        CONCAT('BK', t.BOOK_ID) uniqueNumber,
        t.create_user adviserId,
        t.agent_id agentId,
        t.id bookGroupId,
        t.group_qrcode_name groupQrcodeName,
        t.group_qrcode_url groupQrcodeUrl,
        t.dep_label_id depLabelId,
        t.pro_label_id purLabelId,
        t.create_time createTime
        FROM
        book_group t
        INNER JOIN book t1 ON t.BOOK_ID = t1.BOOK_ID
        WHERE
        t.join_group_type in
        <foreach collection="joinGroupTypes" item="item" open="(" separator="," close=")">
            ${item}
        </foreach>
        <if test="depLabelId!=null">
            AND t.dep_label_id = #{depLabelId}
        </if>
        <if test="proLabelId!=null">
            AND t.pro_label_id = #{proLabelId}
        </if>
        <if test="agentIds!=null and agentIds.size()>0">
            AND t.agent_id IN
            <foreach collection="agentIds" item="item" open="(" separator="," close=")" index=",">
                ${item}
            </foreach>
        </if>
        <if test="bookIds!=null and bookIds.size()>0">
            AND t.BOOK_ID in
            <foreach collection="bookIds" item="item" index="index" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        <if test="name!=null">
            AND t1.BOOK_NAME LIKE CONCAT('%', #{name},'%')
        </if>
        ORDER BY
        t.create_time DESC
    </select>

    <!--个人号社群书统计数量-->
    <select id="countSelfBookGroupStatistics" parameterType="map" resultType="Integer">
        SELECT
        count(1)
        FROM
        book_group t
        INNER JOIN book t1 ON t.BOOK_ID = t1.BOOK_ID
        WHERE
        t.join_group_type=2
        <if test="depLabelId!=null">
            AND t.dep_label_id = #{depLabelId}
        </if>
        <if test="proLabelId!=null">
            AND t.pro_label_id = #{proLabelId}
        </if>
        <if test="agentIds!=null and agentIds.size()>0">
            AND t.agent_id IN
            <foreach collection="agentIds" item="item" open="(" separator="," close=")" index=",">
                ${item}
            </foreach>
        </if>
        <if test="bookIds!=null and bookIds.size()>0">
            AND t.BOOK_ID in
            <foreach collection="bookIds" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="name!=null">
            AND t1.BOOK_NAME LIKE CONCAT('%', #{name},'%')
        </if>
        ORDER BY
        t.create_time DESC
    </select>

    <!--根据类型获取社群书id集合-->
    <select id="getIdsByJoinGroupType" parameterType="map" resultType="long">
        select id from book_group
        where is_delete=0
        <if test="joinGroupType!=null">
            and join_group_type=#{joinGroupType}
        </if>
    </select>

    <insert id="insertQrChangeRecord" parameterType="com.pcloud.book.group.entity.QrChangeRecord">
        insert into qr_change_record(old_scene_id, old_book_group_id, new_book_group_id, create_time)
        values(#{oldSceneId}, #{oldBookGroupId}, #{newBookGroupId}, now())
    </insert>

    <select id="getChangeQrChangeTarget" parameterType="map" resultType="long">
        select new_book_group_id from qr_change_record
        where
        <if test="originType == 'SCENE_ID'">
            old_scene_id = #{originId}
        </if>
        <if test="originType == 'BOOK_GROUP_ID'">
            old_book_group_id = #{originId}
        </if>
        limit 1
    </select>

    <!-- 获取社群书列表(编辑) -->
    <select id="getListBookGroup4Adviser" resultType="com.pcloud.book.group.dto.BookGroupDTO" parameterType="map">
        SELECT
        B.ISBN isbn,
        B.BOOK_NAME bookName,
        CONCAT('BK', G.BOOK_ID) bookNumber,
        G.ID id,
        G.group_qrcode_url groupQrcodeUrl,
        G.group_qrcode_name groupQrcodeName,
        G.scene_id sceneId,
        G.book_id bookId
        FROM book_group G
        INNER JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID
        AND G.CHANNEL_ID = A.CHANNEL_ID
        AND G.CREATE_USER = A.ADVISER_ID
        AND G.IS_DELETE = 0
        INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
        AND B.IS_DELETE = 0
        WHERE
        A.ADVISER_ID = #{adviserId}
        AND A.IS_DELETE = 0
        <if test="name != null">
            AND
            (B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR G.group_qrcode_name LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%') OR CONCAT('BK',A.BOOK_ID) = #{name})
        </if>
        <if test="joinGroupType !=null">
            AND G.join_group_type=#{joinGroupType}
        </if>
        GROUP BY
        G.ID
        ORDER BY
        G.UPDATE_TIME DESC
    </select>

    <!-- 获取关联社群书 -->
    <select id="getAssocBookGroupByAppInfo" resultType="com.pcloud.book.group.dto.BookGroupDTO" parameterType="map">
        SELECT
        G.GROUP_QRCODE_URL groupQrcodeUrl,
        G.GROUP_QRCODE_NAME groupQrcodeName,
        B.BOOK_NAME bookName,
        B.COVER_IMG bookImg,
        G.UPDATE_TIME updateTime,
        G.Id id,
        G.BOOK_ID bookId
        FROM
        BOOK_GROUP G
        INNER JOIN BOOK_GROUP_SERVE S ON S.BOOK_GROUP_ID = G.ID
        INNER JOIN BOOK B ON B.BOOK_ID = G.BOOK_ID
        INNER JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID
        AND G.CHANNEL_ID = A.CHANNEL_ID
        AND G.CREATE_USER = A.ADVISER_ID
        AND G.IS_DELETE = 0
        WHERE
        S.SERVE_ID = #{serveId}
        AND S.SERVE_TYPE = #{serveType}
        AND G.CREATE_USER = #{adviserId}
        GROUP BY
        g.id
        ORDER BY
        G.UPDATE_TIME DESC
    </select>

    <update id="updateSpecialState" parameterType="map">
        UPDATE book_group SET belong_special_agent = #{belongSpecialAgent} WHERE id = #{id}
    </update>


    <select id="getBaseInfoByBookId" parameterType="long" resultType="com.pcloud.book.group.dto.BookGroupStatistic4AgentDTO">
        SELECT
        id bookGroupId,
        group_qrcode_name groupQrcodeName,
        group_qrcode_url groupQrcodeUrl,
        create_time createTime,
        join_group_type joinGroupType
        FROM
        book_group
        WHERE
        is_delete = 0
        AND book_id = #{bookId}
    </select>


    <!--根据书籍简称获取-->
    <select id="getByShortBookName" parameterType="string" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group
        where short_book_name =#{shortBookName}
        and is_delete=0
        limit 1
    </select>

    <select id="listOldLabel" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group
        WHERE pro_label_id>0 AND dep_label_id>0
        AND is_delete=0
    </select>

    <update id="updateSubjectByProLabel" parameterType="map">
        UPDATE book_group g INNER JOIN book_adviser a ON g.book_id=a.BOOK_ID AND g.is_delete=0 AND a.IS_DELETE=0
        SET a.SUB_LABEL_ID= #{subLabelId}
        WHERE g.pro_label_id= #{proLabelId}
    </update>

    <update id="updateGradeByDepLabel" parameterType="map">
        UPDATE book_group g INNER JOIN book_adviser a ON g.book_id=a.BOOK_ID AND g.is_delete=0 AND a.IS_DELETE=0
        SET a.GRA_LABEL_ID=#{graLabelId}
        WHERE g.dep_label_id=#{depLabelId}
    </update>
</mapper>