<?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.GroupQrcodeDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.GroupQrcode">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="classify_id" property="classifyId" jdbcType="BIGINT"/>
        <result column="group_name" property="groupName" jdbcType="VARCHAR"/>
        <result column="group_seq" property="groupSeq" jdbcType="BIGINT"/>
        <result column="user_number" property="userNumber" jdbcType="BIGINT"/>
        <result column="weixin_qrcode_id" property="weixinQrcodeId" jdbcType="BIGINT"/>
        <result column="weixin_group_id" property="weixinGroupId" jdbcType="VARCHAR"/>
        <result column="qrcode_url" property="qrcodeUrl" jdbcType="VARCHAR"/>
        <result column="use_state" property="useState" jdbcType="BIGINT"/>
        <result column="qrcode_state" property="qrcodeState" jdbcType="BIGINT"/>
        <result column="change_name_state" property="changeNameState" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_user" property="updateUser" jdbcType="BIGINT"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="BIT"/>
        <result column="riddle_open" property="riddleOpen" jdbcType="BIT"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,classify_id,group_name,group_seq,user_number,weixin_qrcode_id,weixin_group_id,qrcode_url,use_state,qrcode_state,
        create_user,create_time,update_time,is_delete,riddle_open
    </sql>

    <update id="updateGroupCount" parameterType="map">
        update book_group_qrcode set user_number = #{userNumber} where weixin_group_id = #{weixinGroupId}
    </update>

    <update id="updateGroupQrcode" parameterType="map" >
        update book_group_qrcode set qrcode_url = #{qrcodeUrl}, update_user = #{updateUser}, update_time = now()  where id = #{groupQrcodeId}
    </update>

    <select id="listAllGroupQrcode" resultType="com.pcloud.book.group.vo.ListGroupQrcodeResponseVO">
        SELECT
            a.id AS groupQrcodeId,
            a.create_user AS adviserId,
            a.group_name AS groupName,
            a.qrcode_url AS groupQrcode,
            c.BOOK_NAME AS bookName
        FROM
            book_group_qrcode a
        LEFT JOIN book_group_classify b ON a.classify_id = b.id
        LEFT JOIN book c ON b.book_id = c.BOOK_ID
        LEFT JOIN book_group d ON d.id = b.book_group_id
        WHERE
        a.is_delete = 0
        AND b.is_delete = 0
        AND c.IS_DELETE = 0
        <if test="groupName != null and groupName != '' ">
            AND (
            a.group_name LIKE CONCAT('%', #{groupName},'%')
            or c.BOOK_NAME LIKE CONCAT('%', #{groupName},'%')
            or d.group_qrcode_name LIKE CONCAT('%', #{groupName},'%')
            )
        </if>
        order by a.create_time desc
    </select>

    <select id="getWxGroupIdByServerId" parameterType="long" resultType="com.pcloud.book.group.dto.GroupQrcodeServerDTO">
        SELECT
            a.weixin_group_id AS wxGroupId,
            a.id AS groupQrcodeId,
            b.channel_id AS channelId,
            b.book_group_id AS bookGroupId,
            b.id AS classifyId,
            d.link_url AS linkUrl,
            d.serve_id AS serveId,
            d.serve_type AS serveType
        FROM
            book_group_qrcode a
        LEFT JOIN book_group_classify b ON a.classify_id = b.id
        LEFT JOIN book_keyword c ON b.book_group_id = c.book_group_id
        LEFT JOIN keyword d ON c.keyword_id = d.id
        WHERE
            d.serve_id in
            <foreach collection="list" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            AND a.is_delete = 0
            AND b.is_delete = 0
            AND c.is_delete = 0
            AND d.is_delete = 0
    </select>

    <insert id="insert" parameterType="GroupQrcode" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_group_qrcode
        <trim prefix="(" suffix=")" suffixOverrides=",">
            classify_id,
            group_name,
            group_seq,
            weixin_qrcode_id,
            weixin_group_id,
            qrcode_url,
            qrcode_head_url,
            user_number,
            create_user,
            create_time,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{classifyId,jdbcType=BIGINT},
            #{groupName,jdbcType=VARCHAR},
            #{groupSeq,jdbcType=BIGINT},
            #{weixinQrcodeId,jdbcType=BIGINT},
            #{weixinGroupId,jdbcType=BIGINT},
            #{qrcodeUrl,jdbcType=VARCHAR},
            #{qrcodeHeadUrl,jdbcType=VARCHAR},
            #{userNumber,jdbcType=BIGINT},
            #{createUser,jdbcType=BIGINT},
            NOW(),
        </trim>
    </insert>

    <select id="listQrcodeByClassify" resultType="ClassifyQrcodeVO" parameterType="map">
        SELECT
        id, weixin_qrcode_id weixinQrcodeId, weixin_group_id weixinGroupId, group_name groupName, classify_id classifyId,
        qrcode_url qrcodeUrl, user_number userNumber, ${changeNumber} changeNumber, create_time createdTime
        FROM
        book_group_qrcode
        WHERE
        is_delete = 0 AND classify_id = #{classifyId}
    </select>

     <!--删除分类下微信群-->
    <update id="deleteByClassifyId" parameterType="Long">
        update book_group_qrcode
        <set>
            is_delete = 1,
            update_time = NOW()
        </set>
        where classify_Id = #{_parameter,jdbcType=BIGINT}
    </update>

    <resultMap id="GroupAndUserNumberMap" type="GroupAndUserNumberDTO" >
        <result column="classifyId" property="classifyId" jdbcType="BIGINT" />
        <result column="groupCount" property="groupCount" jdbcType="INTEGER" />
        <result column="userCount" property="userCount" jdbcType="INTEGER" />
    </resultMap>
    <select id="listGroupAndUserNumber" resultMap="GroupAndUserNumberMap" parameterType="list">
        select
        count(1) groupCount,
        SUM(user_number) userCount,
        classify_Id classifyId
        from
        book_group_qrcode
        where
        is_delete = 0
        AND classify_Id in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
        GROUP BY classify_Id
    </select>

    <update id="modifyNameById" parameterType="map">
        update book_group_qrcode
        <set>
            group_name = #{wechatGroupName},
            update_time = NOW(),
            update_user = #{updateUser}
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <update id="updateQrUrl" parameterType="updateQrDTO">
        update
        book_group_qrcode
        SET qrcode_url = #{url}
        WHERE weixin_qrcode_id = #{id}
    </update>

    <select id="getWechatGroupInfo" resultType="ChangeGroupNameDTO" parameterType="list">
        SELECT
        weixin_qrcode_id  id, group_name wxGroupName, wq.weixin_group_id wxGroupId, q.qrcode_url url, wq.robot_wx_id robotWxId
        FROM
        book_group_qrcode q join weixin_qrcode wq on q.weixin_qrcode_id = wq.id
        WHERE
        is_delete = 0 AND weixin_qrcode_id in
        <foreach collection="list" index="index" item="item" open="(" separator=","
                 close=")">
            ${item}
        </foreach>
    </select>

    <update id="syncWeixinGroupId" parameterType="map">
        update
        book_group_qrcode
        <set>
            weixin_group_id = #{weixinGroupId},
            update_time = now()
        </set>
        WHERE weixin_qrcode_id = #{weixinQrcodeId}
    </update>

    <!--根据Id查询详情-->
    <select id="getById" parameterType="Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from book_group_qrcode
        where is_delete=0
        and id=#{id}
    </select>

    <select id="getBaseById" parameterType="Long" resultType="com.pcloud.book.group.vo.GroupQrcodeBaseInfoVO">
        select
            id groupQrcodeId, group_name groupName,qrcode_url qrcodeUrl, weixin_group_id as weixinGroupId, user_number userNumber
        from
            book_group_qrcode
        where
            is_delete=0
        and
            id=#{_parameter}
    </select>

    <update id="updateUserNumber" parameterType="map">
        update
        book_group_qrcode
        <set>
            user_number = #{memberCount},
            update_time = now()
        </set>
        WHERE weixin_group_id = #{weixinGroupId}
    </update>

    <select id="getGroupQrcodeInfo" resultType="GroupQrcodeDTO" parameterType="String">
        SELECT
            q.id,
            c.change_number changeNumber,
            q.use_state useState,
            q.qrcode_state qrcodeState,
            q.weixin_group_id weixinGroupId,
            q.weixin_qrcode_id weixinQrcodeId,
            q.group_name groupName,
            q.user_number userNumber,
            c.id classifyId
        FROM
        book_group_qrcode q
        JOIN book_group_classify c ON q.classify_id = c.id
        AND q.weixin_group_id = #{_parameter} limit 1
    </select>

    <update id="updateUseState" parameterType="map">
        update
            book_group_qrcode
        <set>
            use_state = #{useState},
            update_time = now()
        </set>
        WHERE id = #{id}
    </update>

    <update id="changeQrcodeState" parameterType="map">
        update
        book_group_qrcode
        <set>
            qrcode_state = #{qrcodeState},
            update_time = now()
        </set>
        WHERE id = #{id} and qrcode_state = 1
    </update>

    <update id="changeQrcodeStateForNotOver" parameterType="map">
        update
        book_group_qrcode
        <set>
            qrcode_state = #{qrcodeState},
            update_time = now()
        </set>
        WHERE id = #{id} and qrcode_state = 2
    </update>


    <resultMap id="GroupQrcodeInfoMap" type="GroupQrcodeInfoDTO" >
        <result column="id" property="id" jdbcType="BIGINT" />
        <result column="groupSeq" property="groupSeq" jdbcType="INTEGER" />
        <result column="userNumber" property="userNumber" jdbcType="INTEGER" />
    </resultMap>
    <select id="listQrcodeInfoByIds" resultMap="GroupQrcodeInfoMap" parameterType="list">
        select
        id,
        group_seq groupSeq,
        user_number userNumber,
        qrcode_head_url qrcodeHeadUrl
        from
        book_group_qrcode
        where
        is_delete = 0
        AND id in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
    </select>

    <select id="getUpdateGroupNameParam" resultType="ChangeGroupNameDTO" parameterType="Long">
        select
        wg.id id,
        wg.weixin_group_id wxGroupId,
        wg.robot_wx_id robotWxId
        from
        book_group_qrcode bq join weixin_qrcode wg on bq.weixin_qrcode_id = wg.id
        where bq.id  = #{_parameter}
    </select>

    <select id="getGroupQrcodeInfo4Ad" parameterType="map" resultType="AdGroupQrcodeDTO">
        SELECT
            q.id groupQrcodeId,
            q.group_name groupQrcodeName,
            b.BOOK_ID bookId,
            b.BOOK_NAME bookName,
            b.ISBN isbn,
            c.create_user adviserId,
            c.channel_id channelId
        FROM
            book_group_qrcode q,
            book_group_classify c,
            book b
        WHERE
            q.classify_id = c.id
        AND c.book_id = b.BOOK_ID
        AND q.id = #{qrcodeId}
    </select>

    <!--根据社群书二维码id查询群id集合-->
    <select id="getWeixinGroupIdsByBookGroupId" parameterType="Long" resultType="String">
        SELECT
        DISTINCT t.weixin_group_id
        FROM
        book_group_qrcode t
        INNER JOIN book_group_classify t1 ON t.classify_id = t1.id
        WHERE
        t1.book_group_id = #{bookGroupId}
    </select>

    <!--根据群分类id查询群id集合-->
    <select id="getWeixinGroupIdsByClassifyId" parameterType="Long" resultType="String">
        SELECT
        DISTINCT t.weixin_group_id
        FROM
        book_group_qrcode t
        WHERE
        classify_id = #{classifyId}
    </select>

    <!--根据群号查询-->
    <select id="getGroupQrcodeByGroupId" parameterType="String" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from book_group_qrcode
        where is_delete=0 and weixin_group_id=#{wechatGroupId} limit 1
    </select>

    <select id="getGroupQrcodeByqrcodeId" resultMap="BaseResultMap" parameterType="long">
        select <include refid="Base_Column_List"/> from book_group_qrcode
        where is_delete=0 and id=#{qrcodeId} limit 1
    </select>

    <select id="countByClassifyIds" parameterType="list" resultType="java.lang.Integer">
        select count(1) from book_group_qrcode
        where
        is_delete = 0
        AND classify_Id in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
    </select>

    <!--根据群分类id集合查询群id集合-->
    <select id="getWeixinGroupIdsByClassifyIdList" parameterType="Long" resultType="String">
        SELECT
        DISTINCT t.weixin_group_id
        FROM
        book_group_qrcode t
        WHERE
        classify_id IN
        <foreach collection="list" separator="," close=")" item="item" open="(">
            ${item}
        </foreach>
    </select>

    <!--根据群分类查询id集合-->
    <select id="getIdsByClassifyId" parameterType="Long" resultType="Long">
        select id from book_group_qrcode where classify_id=#{classifyId}
    </select>

    <!--修改群名称-->
    <update id="updateGroupName" parameterType="map">
        update book_group_qrcode
        <set>
            group_name = #{groupName},
            update_time = now()
        </set>
        WHERE id = #{id}
    </update>

    <!--获取群人数大于或等于人数的群-->
    <select id="getListByUserCount" parameterType="Integer" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from book_group_qrcode
        where user_number>=#{userCount} and is_delete=0
    </select>

    <select id="getGroupInfo4AdvertisingByWxGroupIds" resultType="GroupQrcodeInfo4Advertising" parameterType="list">
        SELECT
        g.weixin_group_id wxGroupId,
        g.id groupQrcodeId,
        g.group_name groupName,
        c.create_user adviserId,
        b.BOOK_NAME bookName,
        c.classify classify,
        c.id classifyId,
        g.user_number userNumber,
        g.qrcode_url qrcodeUrl,
        bg.dep_label_id depLabelId,
        bg.pro_label_id proLabelId,
        bg.pur_label_id purLabelId,
        bg.id bookGroupId,
        bg.join_group_type joinGroupType,
        IF (adp.is_open = 0, 0, 1) isOpen
        FROM
        book_group_qrcode g
        LEFT JOIN book_group_classify c 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
        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
        c.create_user IS NOT NULL
        AND g.weixin_group_id IN
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            #{item}
        </foreach>
    </select>

    <select id="listQrcodeIdsByLabelQuery" parameterType="map" resultType="String">
        SELECT
        DISTINCT g.id
        FROM
        book_group_qrcode g
        LEFT JOIN book_group_classify c 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="query != null">
            AND (
            book_name LIKE CONCAT('%', #{query},'%')
            OR group_name LIKE  CONCAT('%', #{query},'%')
            OR classify LIKE  CONCAT('%', #{query},'%')
            )
        </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>
        AND g.weixin_group_id != ""
    </select>

    <!--根据id集合查询微信群相关信息-->
    <select id="getGroupInfoByQrcodeId" parameterType="map" resultType="com.pcloud.book.advertising.dto.WeixinClassifyInfoDTO">
        SELECT
        t.id qrcodeId,
        t.group_name weixinGroupName,
        t1.id classifyId,
        t1.classify classify,
        t2.id bookGroupId,
        t2.group_qrcode_name groupQrcodeName,
        t3.BOOK_ID bookId,
        t3.BOOK_NAME bookName
        FROM
        book_group_qrcode t
        INNER JOIN book_group_classify t1 ON t.classify_id = t1.id
        INNER JOIN book_group t2 ON t1.book_group_id = t2.id
        INNER JOIN book t3 ON t2.book_id = t3.BOOK_ID
        where t.id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            ${item}
        </foreach>
    </select>

    <select id="filterDeleteQrcodeId" resultType="Long" parameterType="list">
        SELECT
        g.id groupQrcodeId
        FROM
        book_group_qrcode g
        LEFT JOIN book_group_classify c ON c.id = g.classify_id
        AND c.is_delete = 0
        AND g.is_delete = 0
        WHERE
        c.create_user IS NOT NULL
        AND g.weixin_group_id IN
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            #{item}
        </foreach>
    </select>

    <select id="listQrcodeByAdviser" parameterType="map" resultType="com.pcloud.book.group.vo.GroupQrcodeBookVO">
        SELECT
        t.id groupQrcodeId,
        t.group_name groupName,
        t.weixin_group_id wxGroupId,
        t2.BOOK_ID bookId,
        t2.BOOK_NAME bookName,
        t2.ISBN isbn
        FROM
        book_group_qrcode t
        INNER JOIN book_group_classify t1 ON t.classify_id = t1.id
        INNER JOIN book t2 ON t1.book_id = t2.BOOK_ID
        INNER JOIN book_group bg ON t1.book_id = bg.book_id
        WHERE
        t1.create_user = #{adviserId}
        AND t.is_delete = 0
        AND t1.is_delete = 0
        AND bg.is_delete = 0
        <if test="name != null">
        AND (
        t2.BOOK_NAME LIKE CONCAT('%', #{name}, '%')
        OR t.group_name LIKE CONCAT('%', #{name}, '%')
        OR t2.ISBN LIKE CONCAT('%', #{name}, '%')
        )
        </if>
    </select>

    <!--根据分类id集合查询-->
    <select id="GroupQrcodeFoAdDTOSByClassifyIds" parameterType="list" resultType="com.pcloud.book.group.dto.GroupQrcodeFoAdDTO">
        SELECT
        t.id qrcodeId,
        t.weixin_group_id weixinGroupId,
        t1.id classifyId,
        t1.book_group_id bookGroupId
        FROM
        book_group_qrcode t
        INNER JOIN book_group_classify t1 ON t.classify_id = t1.id
        WHERE
        t.is_delete = 0
        AND t1.is_delete = 0
        AND t1.id IN
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            #{item}
        </foreach>
    </select>


    <select id="listPageRiddle" parameterType="map" resultType="com.pcloud.book.riddle.dto.GroupRiddleDTO">
        SELECT
        q.group_name AS groupName,
        q.id AS groupQrcodeId,
        q.weixin_group_id AS wxGroupId,
        c.classify AS classify,
        g.group_qrcode_name AS groupQrcodeName,
        b.BOOK_NAME AS bookName,
        g.pro_label_id AS proLabelId,
        g.dep_label_id AS depLabelId,
        g.pur_label_id AS purLabelId,
        q.riddle_open AS riddleOpen
        FROM
        book_group_qrcode q
        LEFT JOIN book_group_classify c ON q.classify_id = c.id
        LEFT JOIN book_group g ON c.book_group_id = g.id
        LEFT JOIN book b ON g.book_id = b.BOOK_ID
        WHERE
        q.create_user = #{partyId}
        AND b.IS_DELETE = 0
        AND g.is_delete = 0
        AND c.is_delete = 0
        AND q.is_delete = 0
        <if test="name != null">
            AND (
            q.group_name LIKE CONCAT('%', #{name}, '%')
            OR c.classify LIKE CONCAT('%', #{name}, '%')
            OR g.group_qrcode_name LIKE CONCAT('%', #{name}, '%')
            OR b.BOOK_NAME LIKE CONCAT('%', #{name}, '%')
            )
        </if>
        ORDER BY q.id DESC
    </select>

    <update id="updateRiddleOpenStatus" parameterType="map">
        UPDATE book_group_qrcode
        SET riddle_open = #{status}
        WHERE
        id = #{qrcodeId}
    </update>

    <select id="listPageGroup4Brand" parameterType="map" resultType="com.pcloud.book.advertising.dto.GroupTagDTO">
        SELECT
        q.weixin_group_id wxGroupId,
        q.id groupQrcodeId,
        q.group_name groupName,
        c.create_user adviserId,
        b.BOOK_NAME bookName,
        c.classify classify,
        c.id classifyId,
        q.user_number userNumber,
        q.qrcode_url qrcodeUrl,
        bg.id bookGroupId,
        bg.join_group_type joinGroupType,
        bg.dep_label_id depLabelId,
        bg.pro_label_id proLabelId,
        bg.pur_label_id purLabelId,
        q.create_time createTime,
        t.create_time tagTime,
        b.ISBN isbn
        FROM
        book_group_qrcode q
        LEFT JOIN book_group_classify c ON c.id = q.classify_id
        LEFT JOIN book b ON c.book_id = b.book_id
        LEFT JOIN book_group bg ON c.book_group_id = bg.id
        LEFT JOIN advertising_group_tag t ON q.id = t.group_qrcode_id
        WHERE
        c.create_user IS NOT NULL
        AND c.is_delete = 0
        AND q.is_delete = 0
        AND b.IS_DELETE = 0
        AND bg.is_delete = 0
        <if test="name != null">
            AND (
            b.BOOK_NAME LIKE CONCAT('%', #{name},'%')
            OR q.group_name LIKE  CONCAT('%', #{name},'%')
            OR c.classify LIKE  CONCAT('%', #{name},'%')
            )
        </if>
        <if test="query != null">
            AND (
            b.BOOK_NAME LIKE CONCAT('%', #{query},'%')
            OR q.group_name LIKE  CONCAT('%', #{query},'%')
            OR b.ISBN LIKE  CONCAT('%', #{query},'%')
            )
        </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>
        <if test="brandId != null">
            AND t.brand_id = #{brandId}
        </if>
        <if test="startTime != null and endTime!= null">
            AND t.create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        GROUP BY q.id
        ORDER BY
        q.create_time DESC, q.id DESC
    </select>

    <select id="getIdsByBookId" resultType="long" parameterType="long">
        SELECT
        q.id
        FROM
        book_group_qrcode q
        LEFT JOIN book_group_classify c ON q.classify_id = c.id
        WHERE
        c.book_id = #{bookId}
    </select>

    <select id="getBookIdByGroupQrcodeId" resultType="long" parameterType="long">
        SELECT
        c.book_id
        FROM
        book_group_qrcode q
        LEFT JOIN book_group_classify c ON q.classify_id = c.id
        WHERE
        q.id = #{qrcodeId}
    </select>

    <select id="getGroupCountUserCountByBookId" resultType="map" parameterType="long">
        SELECT
            COUNT(q.id) groupCount,
            IFNULL(SUM(q.user_number), 0) userNum
        FROM
            book_group_qrcode q
        LEFT JOIN book_group_classify c ON q.classify_id = c.id
        WHERE
            c.book_id = #{bookId}
    </select>


    <select id="getQrcodeByClassify" parameterType="long" resultType="com.pcloud.book.group.vo.ClassifyQrcodeVO">
        select id, classify_id classifyId, group_name groupName, user_number userNumber, weixin_group_id weixinGroupId, create_time createdTime
        from book_group_qrcode where classify_id = #{classifyId}
    </select>

    <select id="getQrcodeByClassifyId" parameterType="long" resultType="com.pcloud.book.group.vo.ClassifyQrcodeVO">
        select id, classify_id as classifyId, group_name as groupName, user_number as userNumber, weixin_qrcode_id as weixinQrcodeId,
        weixin_group_id weixinGroupId, create_time as createdTime, qrcode_state as qrcodeState, qrcode_url as qrcodeUrl
        from book_group_qrcode where classify_id = #{classifyId} and is_delete = 0
    </select>

    <select id="getWechatGroupInfoMap" parameterType="list" resultType="com.pcloud.book.group.dto.GroupQrcodeInfo4Advertising">
        select id groupQrcodeId, group_name groupName, qrcode_url qrcodeUrl from book_group_qrcode where id in
        <foreach collection="list" separator="," open="(" close=")" item="id">
            #{id}
        </foreach>
    </select>

</mapper>