<?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.BookGroupClassifyDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.BookGroupClassify">
        <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="book_group_id" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="classify" property="classify" jdbcType="VARCHAR"/>
        <result column="classify_introduce" property="classifyIntroduce" jdbcType="VARCHAR"/>
        <result column="rank" property="rank" jdbcType="BIGINT"/>
        <result column="price" property="price" jdbcType="DECIMAL"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="product_spec_id" property="productSpecId" jdbcType="BIGINT"/>
        <result column="change_number" property="changeNumber" 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="TINYINT"/>
    </resultMap>

    <resultMap id="BaseResultMapDTO" type="com.pcloud.book.group.dto.ClassifyDTO">
        <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="book_group_id" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="classify" property="classify" jdbcType="VARCHAR"/>
        <result column="classify_introduce" property="classifyIntroduce" jdbcType="VARCHAR"/>
        <result column="rank" property="rank" jdbcType="BIGINT"/>
        <result column="price" property="price" jdbcType="DECIMAL"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="product_spec_id" property="productSpecId" jdbcType="BIGINT"/>
        <result column="change_number" property="changeNumber" jdbcType="BIGINT"/>
        <result column="book_name" property="bookName" jdbcType="VARCHAR"/>
        <result column="book_group_name" property="bookGroupName" jdbcType="VARCHAR"/>
        <result column="book_clock_info_id" property="bookClockInfoId" jdbcType="BIGINT"/>
    </resultMap>

    <insert id="insert" parameterType="BookGroupClassify" useGeneratedKeys="true"
            keyProperty="id">
        INSERT INTO book_group_classify
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_id,
            channel_id,
            book_group_id,
            classify,
            classify_introduce,
            rank,
            price,
            product_id,
            product_spec_id,
            change_number,
            create_user,
            create_time,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookId,jdbcType=BIGINT},
            #{channelId,jdbcType=BIGINT},
            #{bookGroupId,jdbcType=BIGINT},
            #{classify,jdbcType=VARCHAR},
            #{classifyIntroduce,jdbcType=VARCHAR},
            #{rank},
            #{price,jdbcType=DECIMAL},
            #{productId,jdbcType=BIGINT},
            #{productSpecId,jdbcType=BIGINT},
            #{changeNumber,jdbcType=BIGINT},
            #{createUser,jdbcType=BIGINT},
            NOW(),
        </trim>
    </insert>

    <select id="getMaxRank" resultType="Integer" parameterType="Long">
        select
        IFNULL(MAX(rank), 0)+1
        from
        book_group_classify
        where
        book_group_id = #{_parameter,jdbcType=BIGINT}
    </select>


    <!--更新规格-->
    <update id="updateSpecId" parameterType="map">
        update book_group_classify
        <set>
            <if test="productSpecId != null">
                product_spec_id = #{productSpecId,jdbcType=BIGINT},
            </if>
            update_time = NOW()
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>


    <!--更新规格-->
    <update id="update" parameterType="BookGroupClassify">
        update book_group_classify
        <set>
            <if test="classify != null">
            classify = #{classify,jdbcType=VARCHAR},
        </if>
            <if test="classifyIntroduce != null">
                classify_introduce = #{classifyIntroduce},
            </if>
            <if test="price!=null">
                price = #{price},
            </if>
            <if test="changeNumber!=null">
                change_number = #{changeNumber},
            </if>
            update_time = NOW()
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <!--是否有这个分类-->
    <select id="isHaveThisClassify" resultType="Boolean" parameterType="map">
        select
            count(1)
        from
            book_group_classify
        where
            book_group_id = #{bookGroupId,jdbcType=BIGINT}
        and
            classify = #{classify} and is_delete = 0
        <if test="classifyId!=null">
        AND id != #{classifyId}
        </if>
    </select>

     <!--删除分类-->
    <update id="deleteClassify" parameterType="map">
        update book_group_classify
        <set>
            is_delete = 1,
            update_time = NOW()
        </set>
        where id = #{classifyId,jdbcType=BIGINT}
    </update>

    <select id="getClassifyCount" resultType="Integer" parameterType="Long">
        select
        count(1)
        from
        book_group_classify
        where
        book_group_id = #{_parameter,jdbcType=BIGINT} and is_delete = 0
    </select>

    <select id="getById" resultType="ClassifyDTO" parameterType="Long">
        select
            id,
            book_id bookId,
            channel_id channelId,
            book_group_id bookGroupId,
            classify,
            classify_introduce classifyIntroduce,
            price,
            product_id productId,
            product_spec_id productSpecId,
            change_number changeNumber,
            create_user createUser
        from
            book_group_classify
        where
         id = #{_parameter,jdbcType=BIGINT}
    </select>

    <select id="getClassify" resultType="ClassifyVO" parameterType="Long">
        select
            id,
            book_id bookId,
            channel_id channelId,
            book_group_id bookGroupId,
            classify,
            classify_introduce classifyIntroduce,
            price,
            change_number changeNumber,
            create_user
        from
            book_group_classify
        where
            id = #{_parameter,jdbcType=BIGINT}
    </select>


    <select id="listAllClassifyByRank" resultType="ListClassifyVO" parameterType="Long">
        select
            id,
            classify,
            classify_introduce classifyIntroduce,
            price,
            change_number changeNumber,
            rank
        from
            book_group_classify
        where
            book_group_id = #{_parameter,jdbcType=BIGINT} and is_delete = 0  order by rank,id
    </select>

    <select id="listAllClassifyByUser" resultType="ListClassifyVO" parameterType="Long">
        select
            c.id,
            classify,
            classify_introduce classifyIntroduce,
            price,
            change_number changeNumber,
            rank,
            count(1)  qrcodeNumber,
            SUM(user_number) userNumberTotal
        from
            book_group_classify  c join book_group_qrcode q on c.id = q.classify_id
        where
            book_group_id = #{_parameter,jdbcType=BIGINT} and c.is_delete = 0 group by c.id order by userNumberTotal desc,c.id
    </select>

    <select id="getBaseInfoBySpecId" resultType="ClassifyDTO" parameterType="Long">
        select
            id,
            price,
            book_id bookId,
            channel_id channelId,
            book_group_id bookGroupId
        from
            book_group_classify
        where
            product_spec_id = #{_parameter,jdbcType=BIGINT} limit 1
    </select>

    <update id="updateClassifyRank" parameterType="BookGroupClassify">
        update book_group_classify
        <set>
            rank = #{rank},
            update_time = NOW()
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="listClassifyByRank" resultType="ClassifyNameVO" parameterType="Long">
        select
        id,
        classify
        from
        book_group_classify
        where
        book_group_id = #{_parameter,jdbcType=BIGINT} and is_delete = 0  order by rank, id
    </select>

    <select id="listClassifyByUser" resultType="ClassifyNameVO" parameterType="Long">
        select
        c.id,
        classify
        from
        book_group_classify  c left join book_group_qrcode q on c.id = q.classify_id
        where
        book_group_id = #{_parameter,jdbcType=BIGINT} and c.is_delete = 0 group by c.id order by sum(user_number) desc, c.id
    </select>

    <select id="getGroupCount" resultType="Integer" parameterType="Long">
        select
           count(1) groupCount
        from
            book_group_classify  c
        join
            book_group_qrcode q ON c.id = q.classify_id
        where
            c.is_delete = 0
        and
            q.is_delete = 0
        and
            book_group_id = #{_parameter}
    </select>

    <select id="getUserNumberCount" resultType="Integer" parameterType="Long">
        select
            sum(user_number)
        from
            book_group_classify c
        join
            book_group_qrcode q on c.id = q.classify_id
        where
            c.is_delete = 0
        and
            q.is_delete = 0
        and
            book_group_id = #{_parameter}
    </select>

    <select id="getGroupNameAndMaxSeq" resultType="GroupNameAndMaxSeqDTO" parameterType="Long">
        select
            c.classify, g.group_qrcode_name groupQrcodeName, c.id classifyId,
        IFNULL(max(q.group_seq), 0) maxSeq, c.create_user createUser
        from
            book_group_classify c
        join
            book_group g
        on
            c.book_group_id = g.id and c.id =#{_parameter} and c.is_delete = 0
        LEFT JOIN book_group_qrcode q on c.id = q.classify_id  LIMIT 1
    </select>

    <select id="getClassifyQrcodeInfo" resultType="GroupClassifyQrcodeDTO" parameterType="String">
        select
        c.id classifyId,
        c.create_user adviserId,
        c.book_id bookId,
        c.channel_id channelId,
        c.book_group_id bookGroupId,
        c.price price,
        q.id groupQrcodeId,
        c.has_open_learning_report hasOpenLearningReport,
        q.weixin_qrcode_id weixinQrcodeId,
        q.qrcode_url qrCodeUrl
        from
        book_group_classify  c join book_group_qrcode q on c.id = q.classify_id
        and q.weixin_group_id = #{_parameter} limit 1
    </select>

    <select id="listClassifyQrcodeInfo" resultType="GroupClassifyQrcodeDTO" parameterType="list">
        select
            c.id classifyId,
            c.create_user adviserId,
            c.book_id bookId,
            c.channel_id channelId,
            c.book_group_id bookGroupId,
            c.price price,
            c.has_open_learning_report hasOpenLearningReport
        from
            book_group_classify  c
        where c.id in
        <foreach collection="list" open="(" separator="," close=")" item="item">
            #{item}
        </foreach>
    </select>


    <select id="listClassify4Wechat" resultType="ListClassify4WechatVO" parameterType="map">
        SELECT
            c.id classifyId,
            c.book_id bookId,
            c.channel_id channelId,
            c.book_group_id bookGroupId,
            c.classify,
            c.change_number changeNumber,
            c.classify_introduce classifyIntroduce,
            c.price,
            c.product_id productId,
            c.product_spec_id productSpecId,
            max(q.id) groupQrcodeId
        FROM
            book_group_classify c
        JOIN
            book_group_qrcode q
        ON
            c.id = q.classify_id
        AND
            c.book_group_id = #{bookGroupId}
        AND c.is_delete = 0
        AND q.is_delete = 0
        GROUP BY c.id
        <if test="rankType == 0">
            order by sum(q.user_number) desc, c.id
        </if>
        <if test="rankType == 1">
            order by c.rank asc, c.id
        </if>
    </select>

    <select id="getClassifyQrcode" resultType="GroupQrcode4ClassifyVO" parameterType="map">
        SELECT
            classify_id classifyId,
            id groupQrcodeId,
            qrcode_url qrcodeUrl,
            group_name groupName
        FROM
            book_group_qrcode
        WHERE user_number <![CDATA[	 <  ]]> #{changeNumber} AND classify_id = #{classifyId} AND use_state = 1 AND is_delete = 0 limit 1
    </select>

    <select id="listGroupClassifyByAdviser" resultType="com.pcloud.book.group.vo.BookGroupClassifyVO" parameterType="map">
        SELECT
            c.id classifyId,
            c.book_id bookId,
            b.BOOK_NAME bookName,
            c.classify,
            c.book_group_id bookGroupId,
            bg.group_qrcode_name groupQrcodeName,
            sum(IFNULL(g.user_number,0)) userNumber,
            bg.pro_label_id proLabelId,
            bg.dep_label_id depLabelId,
            bg.pur_label_id purLabelId
        FROM book_group_classify c
        INNER JOIN book_group_qrcode g ON c.id = g.classify_id
        LEFT JOIN BOOK_GROUP bg on bg.id = c.book_group_id
        LEFT JOIN book b ON c.book_id = b.book_id
        WHERE c.is_delete = 0
            AND g.is_delete = 0
            AND bg.is_delete = 0
            AND b.is_delete = 0
            AND c.create_user = #{adviserId}
        <if test="bookIds != null">
           AND c.book_id IN
            <foreach collection = "bookIds" index="index" item = "item" open = "("  separator= "," close = ")">
                ${item}
            </foreach>
        </if>
        <if test="name != null">
            AND (
            book_name LIKE CONCAT('%', #{name},'%')
            OR c.classify LIKE  CONCAT('%', #{name},'%')
            )
        </if>
        <if test="proLabelId!=null">
            and bg.pro_label_id=#{proLabelId}
        </if>
        <if test="depLabelId!=null">
            and bg.dep_label_id=#{depLabelId}
        </if>
        <if test="purLabelId!=null">
            and bg.pur_label_id=#{purLabelId}
        </if>
        group by c.id
        order by c.id desc, g.id desc
    </select>

    <select id="listGroupQrcode4Platform" resultType="GroupQrcodeVO" parameterType="map">
        SELECT
            g.id groupQrcodeId,
            g.group_name groupName,
            g.user_number userNumber,
            g.weixin_group_id weixinGroupId,
            c.id classifyId,
            c.book_id bookId,
            c.channel_id channelId,
            c.create_user adviserId,
            b.BOOK_NAME bookName,
            b.ISBN isbn,
            c.classify,
            c.book_group_id bookGroupId,
            g.group_name groupQrcodeName,
            bg.dep_label_id depLabelId,
            bg.pro_label_id proLabelId,
            bg.pur_label_id purLabelId
        FROM
            book_group_classify c
        JOIN book_group_qrcode g ON c.id = g.classify_id
        AND c.is_delete = 0
        AND g.is_delete = 0
        LEFT JOIN book b ON c.book_id = b.book_id
        LEFT JOIN book_group bg ON c.book_group_id = bg.id
        WHERE
        1 = 1
        <if test="name != null">
            AND (
            book_name LIKE CONCAT('%', #{name},'%')
            OR group_name LIKE  CONCAT('%', #{name},'%')
            OR classify LIKE  CONCAT('%', #{name},'%')
            OR b.ISBN like CONCAT('%', #{name},'%')
            )
        </if>
        <if test="depLabelId != null">
            AND bg.dep_label_id = #{depLabelId}
            </if>
        <if test="proLabelId != null">
            AND bg.pro_label_id = #{proLabelId}
        </if>
        <if test="purLabelId != null">
            AND bg.pur_label_id = #{purLabelId}
        </if>
        GROUP BY
            g.id
        ORDER BY
            c.book_group_id ASC,
            c.id ASC,
            g.id ASC
    </select>

    <select id="advertisingWechatGroupList" resultType="AdGroupQrcodeDTO" parameterType="map">
        SELECT
            g.id groupQrcodeId,
            g.group_name groupQrcodeName,
            c.id classifyId,
            c.book_id bookId,
            c.channel_id channelId,
            c.create_user adviserId,
            b.BOOK_NAME bookName,
            b.ISBN isbn,
            c.classify,
            count(DISTINCT db.id) advertisingSpaceNum,
            IF (
                adp.is_open = 0
                OR (
                    adp.is_open IS NULL
                    AND ap.is_open = 0
                ),
                0,
                1
            ) isOpen,
            g.user_number userNumber
        FROM
            book_group_classify c
        JOIN book_group_qrcode g ON c.id = g.classify_id
        AND c.is_delete = 0
        AND g.is_delete = 0
        INNER JOIN advertising_distribution_book db ON db.adviser_id = c.create_user
        AND db.book_id = c.book_id
        AND db.channel_id = c.channel_id
        AND db.classify_id = c.id
        AND db.qrcode_id = g.id
        LEFT JOIN book b ON c.book_id = b.book_id
        LEFT JOIN advertising_agent_permission ap ON ap.agent_id = db.agent_id
        LEFT JOIN advertising_adviser_permission adp ON adp.adviser_id = c.create_user
        AND adp.channel_id = c.channel_id
        AND adp.book_id = c.book_id
        WHERE
            1 = 1
        <if test="name != null">
            AND (
            book_name LIKE CONCAT('%', #{name},'%')
            OR isbn LIKE CONCAT(#{name},'%')
            OR group_name LIKE  CONCAT('%', #{name},'%')
            )
        </if>
        GROUP BY
            g.id
        ORDER BY
            db.id ASC
    </select>

    <resultMap id="BookGroupStatisticMap" type="BookGroupStatisticDTO">
        <id column="bookGroupId" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="groupNumber" property="groupNumber" jdbcType="BIGINT"/>
        <result column="userNumber" property="userNumber" jdbcType="BIGINT"/>
        <result column="classifyCount" property="classifyCount" jdbcType="BIGINT"/>
    </resultMap>
    <select id="getBookGroupStatistic" resultMap="BookGroupStatisticMap" parameterType="list">
        SELECT
            count(1) groupNumber,
            sum(user_number) userNumber,
            count(DISTINCT classify_id) classifyCount,
            book_group_id bookGroupId
        FROM
            book_group_classify c
        JOIN
            book_group_qrcode q ON c.id = q.classify_id
        WHERE
        book_group_id IN
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
        AND  c.is_delete = 0 AND q.is_delete = 0
        GROUP BY  c.book_group_id
    </select>

    <select id="listBookGroupQrcodeStatistic" resultType="BookGroupQrcodeStatisticVO" parameterType="map">
        SELECT
            q.id groupQrcodeId,
            q.classify_id classifyId,
            q.weixin_group_id weixinGroupId,
            q.group_name groupName,
            q.user_number groupUserNumber,
            c.classify
        FROM
            book_group_classify c
        JOIN
            book_group_qrcode q ON c.id = q.classify_id
        WHERE
            c.book_group_id = #{bookGroupId}
        <if test ="classifyIds != null">
            AND c.id in
            <foreach collection = "classifyIds" index="index" item = "item" open = "("  separator= "," close = ")">
                ${item}
            </foreach>
        </if>
        AND c.is_delete = 0 AND q.is_delete = 0
    </select>


    <select id="getStatistic4Adviser" resultType="StatisticVO" parameterType="Long">
        SELECT
            count(1) groupNumber,
            sum(user_number) userNumber,
            count(DISTINCT classify_id) classifyCount
        FROM
            book_group_classify c
        JOIN
            book_group_qrcode q ON c.id = q.classify_id
        join book_adviser ba on c.book_id = ba.book_id and c.channel_id = ba.channel_id and c.create_user = ba.adviser_id
        WHERE
            c.create_user = #{_parameter} AND c.is_delete = 0 and q.is_delete = 0 and ba.is_delete = 0
    </select>

    <resultMap id="GroupQrcodeMap" type="GroupQrcodeBaseDTO">
        <id column="weixin_group_id" property="weixinGroupId" jdbcType="VARCHAR"/>
        <result column="group_name" property="groupName" jdbcType="VARCHAR"/>
        <result column="book_name" property="bookName" jdbcType="VARCHAR"/>
        <result column="classify" property="classify" jdbcType="VARCHAR"/>
        <result column="user_number" property="userNumber" jdbcType="BIGINT"/>
    </resultMap>
    <select id="listGroupQrcodeBaseInfo" resultMap="GroupQrcodeMap" parameterType="list">
        SELECT
            weixin_group_id, group_name, book_name, c.classify, q.user_number
        FROM
            book_group_qrcode q
        JOIN
            book_group_classify c on q.classify_id = c.id
        LEFT JOIN
            book b on c.book_id = b.book_id
        where   weixin_group_id in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            #{item}
        </foreach>
    </select>

    <select id="listAdviserClassify" resultType="AdviserClassifyVO" parameterType="map">
        select
            id classifyId, classify, book_name bookName
        from
            book_group_classify c
        left join
            book b on c.book_id = b.book_id
        where c.create_user = #{adviserId}
        <if test="classify!=null">
            and classify like concat('%', #{classify}, '%')
        </if>
    </select>

    <select id="getProAssocGroupAndUserNumber" resultType="ProAssocGroupAndUserNumberDTO" parameterType="Long">
        SELECT
            count(1) groupCount,
            sum(user_number) userCount
        FROM
            book_group_qrcode
        WHERE
        classify_id IN (
            SELECT
                c.id
            FROM
                book_keyword bk
            JOIN keyword k ON bk.keyword_id = k.id
            JOIN book_group_classify c ON c.book_group_id = bk.book_group_id
            WHERE
                serve_id = #{_parameter}
            AND
                serve_type = 'PRODUCT'
            AND
                bk.classify_id = 0
            UNION
            SELECT
                bk.classify_id
            FROM
                book_keyword bk
            JOIN
            keyword k ON bk.keyword_id = k.id
            WHERE
                serve_id = #{_parameter}
            AND
                serve_type ='PRODUCT'
            AND
                bk.set_type = 1
        ) and is_delete = 0
    </select>

    <!--获取社群书分类列表(编辑)-->
    <select id="listSimpleBookGroupClassify4Adviser" parameterType="map" resultMap="BaseResultMapDTO">
        SELECT
        bgc.id,
        bgc.book_id,
        bgc.book_group_id,
        bgc.classify,
        b.BOOK_NAME
        FROM
        book_group_classify bgc
        LEFT JOIN book b ON bgc.book_id = b.BOOK_ID
        WHERE
        bgc.create_user = #{adviserId}
        AND bgc.is_delete= 0
        <if test="classify != null">
            AND bgc.classify LIKE CONCAT('%', #{classify},'%')
        </if>
        <if test="bookGroupIds != null and bookGroupIds.size > 0">
            AND bgc.book_group_id NOT IN
            <foreach collection="bookGroupIds" separator="," item="item" close=")" open="(">
                ${item}
            </foreach>
        </if>
    </select>

    <!--获取分类名称及对应社群码名称,书籍名称-->
    <select id="getNameWithBookNameByIds" parameterType="list" resultMap="BaseResultMapDTO">
        SELECT
        bgc.id,
        bgc.book_id,
        bgc.book_group_id,
        bgc.classify,
        bg.group_qrcode_name book_group_name,
        b.book_name
        FROM
        book_group_classify bgc
        LEFT JOIN book_group bg ON bgc.book_group_id = bg.id
        LEFT JOIN book b ON bgc.book_id = b.book_id
        WHERE bgc.id IN
        <foreach collection="list" open="(" separator="," item="item" close=")">
            ${item}
        </foreach>
    </select>
    
    <!--获取书下面分类ID集合-->
    <select id="getIdByBookGroupIds" parameterType="list" resultType="long">
        SELECT
        DISTINCT(id)
        FROM
        book_group_classify
        WHERE
        is_delete= 0
        AND
        book_group_id IN
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <!--获取书下面分类ID集合-->
    <select id="getClassifyIdsByBookGroupIds" parameterType="list" resultType="map">
        SELECT
        book_group_id bookGroupId,id
        FROM
        book_group_classify
        WHERE
        is_delete= 0
        AND
        book_group_id IN
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <!--获取分类名称及对应社群码名称-->
    <select id="getNameWithBookClockInfoIdByIds" parameterType="map" resultMap="BaseResultMapDTO">
        SELECT
        bgc.id,
        bgc.book_id,
        bgc.book_group_id,
        bgc.classify,
        bg.group_qrcode_name book_group_name,
        #{bookClockInfoId} book_clock_info_id
        FROM
        book_group_classify bgc
        LEFT JOIN book_group bg ON bgc.book_group_id = bg.id
        WHERE bgc.id IN
        <foreach collection="list" open="(" separator="," item="item" close=")">
            ${item}
        </foreach>
        AND bgc.IS_DELETE = 0
    </select>

    <!--根据bookId获取社群书分类等统计-->
    <select id="getBookGroupStatistics" parameterType="Long" resultType="com.pcloud.book.group.vo.StatisticVO">
        SELECT
        COUNT(DISTINCT t1.id) classifyCount,
        COUNT(DISTINCT t2.id) groupNumber,
        IFNULL(SUM(t2.user_number),0) userNumber
        FROM
        book_group_classify t1
        INNER JOIN book_group_qrcode t2 ON t1.id = t2.classify_id
        WHERE
        t1.is_delete = 0
        AND t2.is_delete = 0
        AND t1.book_id = #{bookId}
    </select>

    <!--根据bookId获取社群书分类集合等统计-->
    <select id="getClassifyStatistics" parameterType="Long" resultType="com.pcloud.book.group.dto.ClassifyKeywordDTO">
        SELECT
        t1.id classifyId,
        t1.classify classify,
        COUNT(DISTINCT t2.id) groupCount,
        SUM(t2.user_number) userCount
        FROM
        book_group_classify t1
        INNER JOIN book_group_qrcode t2 ON t1.id = t2.classify_id
        WHERE
        t1.is_delete = 0
        AND t2.is_delete = 0
        AND t1.book_id = #{bookId}
        group by t1.id
    </select>

    <select id="getClassifyQrcodeInfoByName" resultType="GroupClassifyQrcodeDTO" parameterType="String">
        select
        c.id classifyId,
        c.create_user adviserId,
        c.book_id bookId,
        c.channel_id channelId,
        c.book_group_id bookGroupId,
        c.price price,
        q.id groupQrcodeId,
        q.weixin_qrcode_id weixinQrcodeId,
        q.qrcode_url qrCodeUrl
        from
        book_group_classify  c join book_group_qrcode q on c.id = q.classify_id
        and q.group_name = #{_parameter} limit 1
    </select>
    <!--更新是否开启学习报告-->
    <update id="updateHasOpenLearningReport" parameterType="map">
        update book_group_classify set
        has_open_learning_report=#{hasOpenLearningReport},
        update_user=#{partyId},
        update_time=now()
        where id=#{classifyId}
    </update>

    <!--获取群分类学习报告相关列表-->
    <select id="listClassifyForLearningReport" parameterType="map" resultType="com.pcloud.book.group.dto.ClassifyLearningReportDto">
        SELECT
        c.id classifyId,
        c.book_id bookId,
        b.BOOK_NAME bookName,
        c.classify,
        c.book_group_id bookGroupId,
        bg.group_qrcode_name groupQrcodeName,
        bg.pro_label_id proLabelId,
        bg.dep_label_id depLabelId,
        bg.pur_label_id purLabelId,
        c.has_open_learning_report hasOpenLearningReport
        FROM book_group_classify c
        LEFT JOIN BOOK_GROUP bg on bg.id = c.book_group_id
        LEFT JOIN book b ON c.book_id = b.book_id
        WHERE c.is_delete = 0
        AND bg.is_delete = 0
        AND b.is_delete = 0
        AND c.create_user = #{partyId}
        <if test="keyword != null">
            AND (
            b.book_name LIKE CONCAT('%', #{keyword},'%')
            OR c.classify LIKE  CONCAT('%', #{keyword},'%')
            OR bg.group_qrcode_name LIKE  CONCAT('%', #{keyword},'%')
            )
        </if>
        group by c.id
        order by c.id desc
    </select>
</mapper>