<?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>

    <sql id="Base_Column_List">
        id, book_id, channel_id, book_group_id, classify, classify_introduce, price, product_id, product_spec_id, change_number,
        rank, create_user, update_user, create_time, update_time, is_delete, has_open_learning_report
    </sql>

    <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,
            city_code
        </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(),
            #{cityCode}
        </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}
        AND is_delete = 0
    </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>
            <if test="cityCode != null">
                city_code = #{cityCode},
            </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 createUser,
            city_code cityCode
        from
            book_group_classify
        where
            id = #{_parameter,jdbcType=BIGINT}
    </select>

    <resultMap id="getClassifyByIdsMap" type="com.pcloud.book.group.entity.BookGroupClassify">
        <result property="id" column="id"/>
        <result property="classify" column="classify"/>
    </resultMap>
    <!--根据id获取分类-->
    <select id="getClassifyByIds" parameterType="list" resultMap="getClassifyByIdsMap">
        SELECT
        id id,classify classify
        FROM
        book_group_classify
        WHERE
        is_delete= 0
        AND
        id IN
        <foreach collection="list" index="index" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="listAllClassifyByRank" resultType="ListClassifyVO" parameterType="Long">
        select
            id,
            classify,
            classify_introduce classifyIntroduce,
            price,
            change_number changeNumber,
            rank,
            create_time createdTime,
            city_code cityCode
        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,
            IFNULL(SUM(user_number),0) userNumberTotal,
            c.create_time createdTime,
            city_code cityCode
        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 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,
            g.join_group_type joinGroupType,
            q.group_name groupName,
            c.change_number changeNumber,
            c.classify classify,
            b.BOOK_NAME bookName
        FROM
            book_group_classify c
        JOIN book_group_qrcode q ON c.id = q.classify_id
        JOIN book_group g ON c.book_group_id = g.id
        LEFT JOIN book b ON c.book_id = b.BOOK_ID
        WHERE q.weixin_group_id = #{_parameter}
        AND c.is_delete = 0
        AND g.is_delete = 0
        AND q.is_delete = 0
        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,
            weixin_group_id weixinGroupId,
            group_name groupName
        FROM
            book_group_qrcode
        WHERE user_number <![CDATA[	 <  ]]> #{changeNumber} AND classify_id = #{classifyId} AND use_state = 1 AND is_delete = 0 order by user_number asc 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 AND b.is_delete = 0
        WHERE c.is_delete = 0
            AND g.is_delete = 0
            AND bg.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},'%')
            OR bg.group_qrcode_name 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="listGroupClassify4Clock" resultType="com.pcloud.book.group.vo.BookGroupClassifyVO" parameterType="map">
        SELECT
        c.id classifyId,
        count(DISTINCT g.id) groupsCount,
        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 AND b.is_delete = 0
        LEFT JOIN weixin_clock_group_classify wc ON c.id = wc.book_group_classify_id
        WHERE c.is_delete = 0
        AND g.is_delete = 0
        AND bg.is_delete = 0
        AND wc.weixin_clock_group_classify_id is null
        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(q.id) groupNumber,
            IFNULL(SUM(q.user_number),0) userNumber,
            count(DISTINCT c.id) classifyCount
        FROM
            book_group_classify c
        left JOIN
            book_group_qrcode q ON c.id = q.classify_id
        AND q.is_delete = 0
        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 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>
        <if test="bookId != null">
            AND c.book_id = #{bookId}
        </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,
        g.join_group_type joinGroupType,
        t1.book_group_id bookGroupId
        FROM
        book_group_classify t1
        LEFT JOIN book_group_qrcode t2 ON t1.id = t2.classify_id
        AND t2.is_delete = 0
        LEFT JOIN book_group g ON t1.book_group_id = g.id
        WHERE
        t1.is_delete = 0
        AND g.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,
        IFNULL(SUM(t2.user_number), 0) userCount,
        t1.book_group_id bookGroupId
        FROM
        book_group_classify t1
        LEFT JOIN book_group_qrcode t2 ON t1.id = t2.classify_id
        AND t2.is_delete = 0
        LEFT JOIN book_group g ON t1.book_group_id = g.id
        WHERE
        t1.is_delete = 0
        AND g.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>

    <select id="getGroupClassifyCount" parameterType="list" resultType="map">
        select book_id bookId, count(1) classifyCount, book_group_id bookGroupId from book_group_classify
        where is_delete = 0 and book_id in
        <foreach collection="list" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
        group by book_id
    </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>

    <insert id="addClickClassify" parameterType="map">
        insert into book_group_classify_click(book_group_id, classify_id,create_user, create_time)
        values(#{bookGroupId},#{classifyId},#{wechatUserId},now())
    </insert>

    <select id="mapClickClassify" parameterType="map" resultType="com.pcloud.book.group.dto.ClickClassifyDTO">
        select classify_id classifyId, count(1) count from book_group_classify_click
        where classify_id in
        <foreach collection="classifyIds" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
        group by classify_id
    </select>

    <select id="getClassifyIngoreDelete" parameterType="long" resultType="com.pcloud.book.group.vo.ListClassifyVO">
        select id,classify from book_group_classify where book_group_id = #{bookGroupId}
    </select>

    <!--获取群分类学习报告相关列表-->
    <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 AND b.is_delete = 0
        WHERE c.is_delete = 0
        AND bg.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>


    <!--拖动排序-->
    <update id="dragSortClassify" parameterType="map">
        update book_group_classify
        set rank = case
        <foreach collection="classifyIds" index="index" item="classifyId">
            when id = #{classifyId} then #{index} + 1
        </foreach>
        end,
        update_time = now()
        where id in
        <foreach collection="classifyIds" item="classifyId" separator="," open="(" close=")">
            #{classifyId}
        </foreach>
    </update>

    <select id="pageClassify" parameterType="map" resultType="com.pcloud.book.group.vo.GroupStatisticVO">
        select bgc.id classifyId, bgc.classify classifyName, bgc.price price,sum(user_number) groupPersonCount, bgc.create_time createdTime
        from book_group_classify bgc left join book_group_qrcode bgq on bgc.id = bgq.classify_id
        where book_group_id = #{bookGroupId}
        group by bgc.id
    </select>

    <!--根据社群码id集合获取-->
    <select id="getListByBookGroupId" parameterType="list" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        book_group_classify t
        WHERE
        t.book_group_id =#{bookGroupId}
        AND t.is_delete = 0
        order by create_time asc
    </select>

    <!--根据类型获取分类数量-->
    <select id="getClassifyCountByJoinGroupType" parameterType="map" resultType="Integer">
        SELECT
        COUNT(1)
        FROM
        book_group_classify t
        INNER JOIN book_group t1 ON t.book_group_id = t1.id
        WHERE
        t.is_delete = 0
        AND t1.is_delete = 0
        <if test="joinGroupType!=null">
            and t1.join_group_type=#{joinGroupType}
        </if>
    </select>

    <select id="listClassifyCountInfo" parameterType="map" resultType="com.pcloud.book.group.dto.SelfBookGroupStDTO">
        SELECT
        t.book_group_id bookGroupId,
        COUNT(DISTINCT t.id) classifyCount,
        IFNULL(SUM(t1.user_number),0) preInGroupPeopleCount
        FROM
        book_group_classify t
        LEFT JOIN book_group_qrcode t1 ON t.id = t1.classify_id
        AND t1.is_delete = 0
        WHERE
        t.book_group_id IN
        <foreach collection="bookGroupIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        AND t.is_delete = 0
        GROUP BY
        t.book_group_id
    </select>

    <!--查询群分类数量等信息-->
    <select id="getClassifyCountAbout" resultMap="BookGroupStatisticMap" parameterType="list">
        SELECT
        count(t1.id) groupNumber,
        t.book_group_id bookGroupId,
        COUNT(DISTINCT t.id) classifyCount,
        IFNULL(SUM(t1.user_number),0) userNumber
        FROM
        book_group_classify t
        LEFT JOIN book_group_qrcode t1 ON t.id = t1.classify_id
        AND t1.is_delete = 0
        WHERE
        t.book_group_id IN
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
        AND t.is_delete = 0
        GROUP BY
        t.book_group_id
    </select>

    <select id="getBookGroupClassifyByAdviserId" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupClassifyDTO">
        SELECT
        t.id classifyId,
        t.classify classify,
        count(t1.id) groupNumber
        FROM
        book_group_qrcode t1
        LEFT JOIN book_group_classify t ON t.id = t1.classify_id
        AND t1.is_delete = 0
        WHERE
        t.create_user=#{adviserId}
        <if test="classify!=null">
            and t.classify LIKE CONCAT("%",#{classify},"%")
        </if>
        AND t.is_delete = 0
        and t.price=0
        GROUP BY
        t.id
        ORDER BY t.create_time DESC
    </select>

    <select id="getBookGroupClassifyByClassifyIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupClassifyDTO">
        SELECT
        t.id classifyId,
        t.classify classify,
        count(t1.id) groupNumber
        FROM
        book_group_qrcode t1
        LEFT JOIN book_group_classify t ON t.id = t1.classify_id
        AND t1.is_delete = 0
        WHERE
        t.id in
        <foreach collection = "classifyIds"  item = "item" open = "("  separator= "," close = ")">
            #{item}
        </foreach>
        AND t.is_delete = 0
        and t.price=0
        GROUP BY
        t.id
        ORDER BY t.create_time DESC
    </select>

    <select id="getQrcodeByClassifyId4Heals" parameterType="map" resultType="com.pcloud.book.group.dto.GroupQrcode4ClassifyDTO">
        SELECT
        id groupQrcodeId,
        qrcode_url qrcodeUrl,
        weixin_group_id weixinGroupId,
        group_name groupName
        FROM
        book_group_qrcode
        WHERE user_number <![CDATA[	 <  ]]> #{changeNumber} AND classify_id = #{classifyId} AND use_state = 1 AND is_delete = 0 order by user_number asc
    </select>

    <select id="listGroupClassify4Pcloud" parameterType="map" resultType="com.pcloud.book.group.vo.PcloudBookGroupClassifyVO">
        SELECT
            a.id,
            a.classify,
            a.classify_introduce AS classifyIntroduce,
            a.price,
            a.create_user as adviserId,
            count(b.classify_id) AS groupCount,
            c.agent_id as agentId,
            c.group_qrcode_name as groupQrcodeName,
            city_code cityCode
        FROM
            book_group_classify a
        LEFT JOIN book_group_qrcode b ON a.id = b.classify_id AND b.is_delete = 0 AND a.is_delete = 0
        LEFT JOIN book_group c ON a.book_group_id = c. id
        <where>
            <if test="name!=null or list.size()>0">
                <trim prefix="(" suffix=")" prefixOverrides="OR">
                    <if test="name!=null">
                        a.classify LIKE CONCAT("%",#{name},"%")
                    </if>
                    <if test="list.size()>0">
                        OR a.book_id IN
                        <foreach collection="list" open="(" separator="," close=")" index="index" item="item">
                            ${item}
                        </foreach>
                    </if>
                </trim>
            </if>
            <if test="proLabelId!=null">
                AND c.pro_label_id = #{proLabelId}
            </if>
            <if test="depLabelId!=null">
                AND c.dep_label_id = #{depLabelId}
            </if>
            <if test="cityCode != null">
                and city_code = #{cityCode}
            </if>
            <if test="purLabelId!=null">
                AND c.pur_label_id = #{purLabelId}
            </if>
            <if test="agentId!=null">
                AND c.agent_id = #{agentId}
            </if>
            <if test="adviserId!=null">
                AND a.create_user = #{adviserId}
            </if>
        </where>
        GROUP BY b.classify_id
        HAVING groupCount > 0
        ORDER BY a.create_time DESC
    </select>

    <select id="listGroupClassify4Analysis" parameterType="map" resultType="com.pcloud.book.group.vo.BookGroupClassifyAnalysisVO">
    SELECT * FROM(
        <if test="linkOnly == null or linkOnly == 0">
        SELECT
            a.id,
            a.classify classifyName,
            a.classify_introduce AS `desc`,
            c.pro_label_id AS proLabelId,
            c.dep_label_id AS depLabelId,
            c.pur_label_id AS purLabelId,
            0 firstClassify,
            0 secondClassify,
            0 gradeLabelId,
            0 subjectLabelId,
            0 groupType,
            city_code cityCode,
            a.create_time
        FROM book_group_classify a
            LEFT JOIN book_group_qrcode b ON a.id = b.classify_id AND b.is_delete = 0 AND a.is_delete = 0
            LEFT JOIN book_group c ON a.book_group_id = c.id
        <where>
            a.is_delete=0
            <if test="firstClassify != null">
                AND 1=2
            </if>
            <if test="secondClassify != null">
                AND 1=2
            </if>
            <if test="gradeLabelId != null">
                AND 1=2
            </if>
            <if test="subjectLabelId != null">
                AND 1=2
            </if>
        </where>
        GROUP BY b.classify_id
        HAVING count(b.classify_id) > 0
        UNION
        </if>
        SELECT
            a.id,
            a.`name` classifyName,
            a.description as `desc`,
            a.pro_label_id AS proLabelId,
            a.dep_label_id AS depLabelId,
            a.pur_label_id AS purLabelId,
            a.first_classify AS firstClassify,
            a.second_classify AS secondClassify,
            a.grade_label_id AS gradeLabelId,
            a.subject_label_id AS subjectLabelId,
            a.group_type as groupType,
            '' cityCode,
            a.create_time
        FROM pcloud_group_activity a
        WHERE
            a.is_delete = 0
            AND a.group_type = 1
            <if test="firstClassify != null">
                AND a.first_classify = #{firstClassify}
            </if>
            <if test="secondClassify != null">
                AND a.second_classify = #{secondClassify}
            </if>
            <if test="gradeLabelId != null">
                AND a.grade_label_id = #{gradeLabelId}
            </if>
            <if test="subjectLabelId != null">
                AND a.subject_label_id = #{subjectLabelId}
            </if>
    ) temp_tb
        <where>
            <if test="name!=null">
                classifyName LIKE CONCAT("%",#{name},"%")
            </if>
            <if test="proLabelId!=null">
                AND proLabelId = #{proLabelId}
            </if>
            <if test="depLabelId!=null">
                AND depLabelId = #{depLabelId}
            </if>
            <if test="purLabelId!=null">
                AND purLabelId = #{purLabelId}
            </if>
            <if test="cityCode != null">
                and cityCode = #{cityCode}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>

    <select id="listClassify4Heals" resultType="com.pcloud.book.group.dto.ListClassify4WechatDTO" 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
        where
        c.is_delete = 0
        AND q.is_delete = 0
        and c.id in
        <foreach collection="classifyIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        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="getQrcodeByClassifyIds4Heals" parameterType="map" resultType="com.pcloud.book.group.dto.GroupQrcode4ClassifyDTO">
        SELECT
        a.id groupQrcodeId,
        a.qrcode_url qrcodeUrl,
        a.weixin_group_id weixinGroupId,
        a.group_name groupName
        FROM
        book_group_qrcode a
        LEFT JOIN book_group_classify b on a.classify_id=b.id
        where b.id in
        <foreach collection="classifyIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        and a.user_number <![CDATA[	 <  ]]> b.change_number AND a.use_state = 1 AND a.is_delete = 0 order by a.user_number asc
    </select>

    <select id="getBookAndAdviserByClassifyIds" parameterType="map" resultType="com.pcloud.book.es.dto.BookAdviserInfo">
        SELECT
            a.id AS classifyId,
            a.create_user AS adviserId,
            b.BOOK_NAME AS bookName,
            b.ISBN AS isbn
        FROM
            book_group_classify a
            LEFT JOIN book b ON a.book_id = b.BOOK_ID
        WHERE
            a.id IN
            <foreach collection="classifyIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
    </select>


</mapper>
