<?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"/>
    </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
    </sql>

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


    <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
        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="GroupQrcodeBaseInfoVO">
        select
            id groupQrcodeId, group_name groupName,qrcode_url qrcodeUrl, weixin_group_id as weixinGroupId
        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,
            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>


    <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="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,
        g.user_number userNumber,
        g.qrcode_url qrcodeUrl,
        bg.dep_label_id depLabelId,
        bg.pro_label_id proLabelId,
        bg.pur_label_id purLabelId,
        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>
</mapper>