<?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.BookGroupCipherUserDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.BookGroupCipherUser">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_group_id" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="book_group_cipher" property="bookGroupCipher" jdbcType="VARCHAR"/>
        <result column="wx_user_id" property="wxUserId" jdbcType="VARCHAR"/>
        <result column="alt_id" property="altId" jdbcType="VARCHAR"/>
        <result column="short_book_name" property="shortBookName" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>


    <sql id="Base_Column_List">
        id, book_group_id, book_group_cipher, wx_user_id, alt_id, short_book_name, create_time
    </sql>

    <insert id="insert" parameterType="com.pcloud.book.group.entity.BookGroupCipherUser" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_group_cipher_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_group_id,
            book_group_cipher,
            wx_user_id,
            alt_id,
            short_book_name,
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookGroupId,jdbcType=BIGINT},
            #{bookGroupCipher,jdbcType=VARCHAR},
            #{wxUserId,jdbcType=VARCHAR},
            #{altId,jdbcType=VARCHAR},
            #{shortBookName,jdbcType=VARCHAR},
            NOW()
        </trim>
    </insert>

    <!--根据用户查询社群码id-->
    <select id="getBookGroupIdByWxUserId" parameterType="String" resultType="long">
        select book_group_id from book_group_cipher_user t where t.wx_user_id=#{wxUserId} order by create_time desc limit 1
    </select>

    <select id="getFriendsCountByBookGroup" parameterType="long" resultType="integer">
        SELECT
        COUNT(DISTINCT wx_user_id)
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
    </select>

    <select id="getBookGroupFriendsCountByDay" parameterType="map" resultType="com.pcloud.book.group.dto.DayCountDTO">
        SELECT
        COUNT(DISTINCT a.wx_user_id) count,
        DATE_FORMAT(a.create_time, "%Y-%m-%d") date
        FROM
        (
        SELECT
            wx_user_id,create_time
        FROM
            book_group_cipher_user
        WHERE
            book_group_id = #{bookGroupId}
        GROUP BY
            wx_user_id
        ) AS a
        WHERE
        a.create_time >= #{startTime}
        AND a.create_time &lt;= #{endTime}
        GROUP BY
        DATE_FORMAT(a.create_time, "%Y-%m-%d")
    </select>

    <select id="listPageFriendsStatistic" parameterType="map" resultType="com.pcloud.book.group.vo.FriendsVO">
        SELECT
        wx_user_id wxUserId,
        alt_id robotId,
        create_time createTime
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
        GROUP BY
        wx_user_id,
        alt_id
        ORDER BY
        create_time DESC,id DESC
    </select>

    <select id="getFriendsCountByBookGroupByTime" resultType="integer" parameterType="map">
        SELECT
        COUNT(DISTINCT wx_user_id)
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
        <if test="startTime != null and endTime != null">
         AND create_time BETWEEN #{startTime} AND #{endTime}
        </if>
    </select>

    <!--查询社群书关联的用户id集合-->
    <select id="getWxUserIdAndAltsByBookGroupId" parameterType="map" resultMap="BaseResultMap">
        SELECT t.wx_user_id,t.alt_id
        FROM book_group_cipher_user t
        where t.book_group_id=#{bookGroupId}
        GROUP BY t.wx_user_id
    </select>

    <select id="getFriendIdsByBookGroupId" resultType="string" parameterType="long">
        SELECT DISTINCT
        wx_user_id
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
    </select>

    <select id="listPageFriendInfo" parameterType="map" resultType="com.pcloud.book.group.dto.SelfRobotUserDTO">
        SELECT
        u.wx_user_id wxUserId,
        u.alt_id robotId,
        u.create_time joinTime
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        WHERE
        1 = 1
        <if test="userIds != null and userIds.size>0">
            AND u.wx_user_id IN
            <foreach collection = "userIds" index="index" item = "userId" open = "("  separator= "," close = ")">
                #{userId,jdbcType=VARCHAR}
            </foreach>
        </if>
        <if test="robotIds != null and robotIds.size>0">
            AND u.alt_id IN
            <foreach collection="robotIds" index="index" item="robotId" open = "("  separator= "," close = ")">
                #{robotId, jdbcType=VARCHAR}
            </foreach>
        </if>
        <if test="bookIds != null and bookIds.size>0">
            AND g.book_id IN
            <foreach collection="bookIds" index="index" item="bookId" open = "("  separator= "," close = ")">
                ${bookId}
            </foreach>
        </if>
        <if test="null != friendState and (friendState==1 or friendState==2)">
            <if test="friendState==2">
                AND (
            </if>
            <if test="friendState==1">
                AND !(
            </if>
            <foreach collection="deleteUserList" index="index" item="item" open="(" separator="or" close=")">
                u.wx_user_id = #{item.wxUserId} AND u.alt_id = #{item.robotId}
            </foreach>
            )
        </if>
        GROUP BY
        u.wx_user_id,
        u.alt_id
        ORDER BY
        u.create_time DESC
        <if test="pageNum>=0 and numPerPage>0">
            LIMIT #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="getBookGroupListByUser" resultType="com.pcloud.book.book.dto.BookDto" parameterType="map">
        SELECT
        u.book_group_id bookGroupId,
        b.BOOK_NAME bookName,
        b.ISBN isbn,
        b.SERIAL_NUMBER serialNumber
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        LEFT JOIN book b ON g.book_id = b.BOOK_ID
        WHERE
        u.wx_user_id = #{wxUserId}
        AND u.alt_id = #{altId}
        GROUP BY
        u.book_group_id
    </select>

    <!--获取个人号累计加好友人数-->
    <select id="getAddFriendCount" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
          (
            SELECT
              COUNT(1)
            FROM
              book_group_cipher_user t
            GROUP BY
              book_group_id,
              wx_user_id
          ) t
    </select>

    <!--根据时间段获取个人号加好友人数-->
    <select id="getAddFriendCountByTime" parameterType="map" resultType="com.pcloud.book.group.dto.CountAndTimeDTO">
        SELECT
        COUNT(1) count,
        <if test="timeType==1">
            DATE_FORMAT(tt.create_time,'%Y-%m-%d') time
        </if>
        <if test="timeType==2">
            DATE_FORMAT(tt.create_time,'%Y-%m-%d %H') time
        </if>
        FROM
        (
        SELECT
        t.id,t.create_time
        FROM
        book_group_cipher_user t
        WHERE
        1=1
        <if test="startTime!=null">
            and t.create_time >= #{startTime}
        </if>
        <if test="endTime!=null">
            AND t.create_time &lt;= #{endTime}
        </if>
        GROUP BY
        t.book_group_id,
        t.wx_user_id
        ORDER BY
        create_time ASC
        ) tt
        <if test="timeType==1">
            GROUP BY DATE_FORMAT(tt.create_time,'%Y-%m-%d')
        </if>
        <if test="timeType==2">
            GROUP BY DATE_FORMAT(tt.create_time,'%Y-%m-%d %H')
        </if>
    </select>

    <!--获取个人号读者专业或深度分布-->
    <select id="getUserLabelDistribution" parameterType="map" resultType="com.pcloud.book.group.dto.LabelUserCountDTO">
        SELECT
            COUNT(tt.wx_user_id) count,
        <if test="type==1">
            tt.pro_label_id labelId
        </if>
        <if test="type==2">
            tt.dep_label_id labelId
        </if>
        FROM
        (
            SELECT
                t.book_group_id,
                t.wx_user_id,
                t1.dep_label_id,
                t1.pro_label_id
            FROM
                book_group_cipher_user t
            INNER JOIN book_group t1 ON t.book_group_id = t1.id
            WHERE
                t1.dep_label_id IS NOT NULL
                AND t1.pro_label_id IS NOT NULL
                <if test="altIds!=null and altIds.size()>0">
                    AND alt_id IN
                    <foreach collection="altIds" item="item" open="(" separator="," close=")" index="index">
                        #{item}
                    </foreach>
                </if>
            GROUP BY
            t.book_group_id,
            t.wx_user_id
        ) tt
        <if test="type==1">
            GROUP BY tt.pro_label_id
        </if>
        <if test="type==2">
            GROUP BY tt.dep_label_id
        </if>
        ORDER BY
        count DESC
    </select>

    <!--根据社群码获取小号-->
    <select id="getAltIdByBookGroupIds" parameterType="map" resultMap="BaseResultMap">
        select book_group_id, alt_id
        from book_group_cipher_user where
        book_group_id in
        <foreach collection="bookGroupIds" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
        group by book_group_id,alt_id
    </select>

    <!--根据bookGroupIds获取累计加好友人数-->
    <select id="getAddFriendByBookGroupIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupIdAndCountDTO">
        SELECT
        tt.book_group_id bookGroupId,
        COUNT(wx_user_id) count
        FROM
        (
        SELECT
        t.book_group_id,
        t.wx_user_id
        FROM
        book_group_cipher_user t
        WHERE
        t.book_group_id IN
        <foreach collection="bookGroupIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        GROUP BY
        t.book_group_id,
        t.wx_user_id
        ) tt
        GROUP BY
        tt.book_group_id
    </select>

    <!--根据社群码id集合获取小号和用户id-->
    <select id="getWxUserIdAndAltsByBookGroupIds" parameterType="map" resultMap="BaseResultMap">
        SELECT
        t.book_group_id ,
        t.alt_id ,
        t.wx_user_id
        FROM
        book_group_cipher_user t
        WHERE
        t.book_group_id IN
        <foreach collection="bookGroupIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        GROUP BY
        t.book_group_id,
        t.alt_id,
        t.wx_user_id
    </select>

    <!--根据小号获取服务社群书数量-->
    <select id="getBookGroupCountListByAltIds" parameterType="list" resultType="com.pcloud.book.group.dto.AltAndCountDTO">
        SELECT
        tt.alt_id altId,
        count(1) count
        FROM
        (
        SELECT
        t.book_group_id,
        t.alt_id
        FROM
        book_group_cipher_user t
        WHERE
        alt_id IN
        <foreach collection="list" open="(" separator="," close=")" item="item">
            #{item}
        </foreach>
        GROUP BY
        t.book_group_id,t.alt_id
        ) tt
        GROUP BY
        tt.alt_id
    </select>

    <!--获取小号社群书数量-->
    <select id="getBookGroupIdsByAltIds" parameterType="list" resultType="Long">
        SELECT DISTINCT
        book_group_id
        FROM
        book_group_cipher_user t
        WHERE
        alt_id IN
        <foreach collection="list" open="(" separator="," close=")" item="item">
            #{item}
        </foreach>
    </select>

    <!--根据出版社id获取小号id集合-->
    <select id="getAltIdsByAgentId" parameterType="Long" resultType="String">
        SELECT
        DISTINCT alt_id
        FROM
        book_group_cipher_user t
        LEFT JOIN book_group t1 ON t.book_group_id = t1.id
        WHERE
        t1.agent_id = #{agentId}
    </select>

    <select id="listUser4SelfPush" resultType="com.pcloud.book.group.vo.UserBookInfoVO" parameterType="map">
        SELECT
        u.wx_user_id wxUserId,
        u.alt_id altId
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        WHERE
        u.alt_id = #{altId}
        <if test="proLabelId != null">
            AND g.pro_label_id = #{proLabelId}
        </if>
        <if test="depLabelId != null">
            AND g.dep_label_id = #{depLabelId}
        </if>
        <if test="bookIds != null and bookIds.size>0">
            AND g.book_id IN
            <foreach collection="bookIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="wxUserIds != null and wxUserIds.size>0">
            AND u.wx_user_id IN
            <foreach collection="wxUserIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
        GROUP BY
        u.wx_user_id
        ORDER BY
        u.create_time DESC,
        u.id DESC
        <if test="pageNum>=0 and numPerPage>0">
            LIMIT #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="listUser4SelfPushByBookGroupId" resultType="com.pcloud.book.group.vo.UserBookInfoVO" parameterType="map">
        SELECT
        u.wx_user_id wxUserId,
        u.alt_id altId
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        WHERE
        g.book_id = #{bookId}
        <if test="wxUserIds != null and wxUserIds.size>0">
            AND u.wx_user_id IN
            <foreach collection="wxUserIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
        GROUP BY
        u.wx_user_id
        ORDER BY
        u.create_time DESC,
        u.id DESC
        <if test="pageNum>=0 and numPerPage>0">
            LIMIT #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="getScanBookInfoByUser" parameterType="map" resultType="com.pcloud.book.group.vo.UserBookInfoItemVO">
        SELECT
        MAX(u.id) max,
        u.book_group_id bookGroupId,
        b.BOOK_NAME bookName,
        g.pro_label_id proLabelId,
        g.dep_label_id depLabelId,
        g.create_user adviserId,
        g.book_id bookId
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        LEFT JOIN book b ON g.book_id = b.BOOK_ID
        WHERE
        u.wx_user_id = #{wxUserId}
        AND u.alt_id = #{altId}
        GROUP BY
        u.book_group_id
        ORDER BY
        max DESC
    </select>

    <select id="getFriendIdListByBookAndRobot" parameterType="map" resultType="string">
        SELECT DISTINCT
        u.wx_user_id
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        WHERE
        u.alt_id = #{altId}
        <if test="bookIds != null and bookIds.size>0">
            AND g.book_id IN
            <foreach collection="bookIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getLatestCipherBookGroup" parameterType="map" resultType="long">
        SELECT
        book_group_id
        FROM
        book_group_cipher_user
        WHERE
        alt_id = #{altId}
        AND wx_user_id = #{wxUserId}
        ORDER BY
        id DESC
        LIMIT 1
    </select>

    <!--根据wxUserId获取-->
    <select id="getByWxUserId" parameterType="string" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from book_group_cipher_user where
        wx_user_id=#{wxUserId}
        order by create_time desc
        limit 1
    </select>

    <select id="getSelfRobotBookGroupInfoByUser" parameterType="map" resultType="com.pcloud.book.group.dto.SelfRobotBookGroupDTO">
        SELECT
        u.book_group_id bookGroupId,
        g.group_qrcode_name groupQrcodeName,
        u.alt_id wxId,
        g.book_id bookId,
        g.create_user adviserId
        FROM
        book_group_cipher_user u
        LEFT JOIN book_group g ON u.book_group_id = g.id
        WHERE
        u.book_group_id = #{bookGroupId}
        AND u.wx_user_id = #{wxUserId}
        ORDER BY
        u.id DESC
        LIMIT 1
    </select>

    <select id="getAltCountByBookGroup" parameterType="long" resultType="integer">
        SELECT
        COUNT(DISTINCT alt_id)
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
    </select>

    <select id="getAltFriendList" parameterType="long" resultType="com.pcloud.book.group.dto.AltAndCountDTO">
        SELECT
        alt_id altId,
        COUNT(DISTINCT wx_user_id) friendCount
        FROM
        book_group_cipher_user
        WHERE
        book_group_id = #{bookGroupId}
        GROUP BY
        alt_id
    </select>

    <!--只查个人号1.0版本读者-->
    <select id="listUser4ReadingActivity" parameterType="map" resultType="com.pcloud.book.group.vo.UserBookInfoVO">
        SELECT
        s.wx_user_id wxUserId,
        s.alt_id altId
        FROM
        book_group_cipher_user s
        JOIN (
            SELECT
            MAX(u.id) uid
            FROM
            book_group_cipher_user u
            LEFT JOIN book_group g ON u.book_group_id = g.id
            WHERE
            g.join_group_type = 3
            <if test="bookIds != null and bookIds.size>0">
                AND g.book_id IN
                <foreach collection="bookIds" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="wxUserIds != null and wxUserIds.size>0">
                AND u.wx_user_id IN
                <foreach collection="wxUserIds" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="robotId != null">
                AND u.alt_id = #{robotId}
            </if>
            <if test="invitedFilter != null and invitedFilter == 1">
                AND u.wx_user_id NOT IN
                (SELECT DISTINCT wx_user_id FROM reading_user)
            </if>
            GROUP BY
            u.wx_user_id
        ) AS a ON s.id = a.uid
        GROUP BY
        s.wx_user_id
        ORDER BY
        s.create_time DESC,
        s.id DESC
        <if test="pageNum>=0 and numPerPage>0">
            LIMIT #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="listId4ReadingActivity" parameterType="map" resultType="Long">
        SELECT
        MAX(u.id) uid
        FROM
        book_group_cipher_user u
        INNER JOIN book_group g ON u.book_group_id = g.id
        WHERE
        g.join_group_type = 3
        <if test="bookIds != null and bookIds.size>0">
            AND g.book_id IN
            <foreach collection="bookIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="wxUserIds != null and wxUserIds.size>0">
            AND u.wx_user_id IN
            <foreach collection="wxUserIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="robotId != null">
            AND u.alt_id = #{robotId}
        </if>
        <if test="invitedFilter != null and invitedFilter == 1">
            AND u.wx_user_id NOT IN
            (SELECT DISTINCT wx_user_id FROM reading_user)
        </if>
        GROUP BY
        u.wx_user_id
        ORDER BY
        u.create_time DESC,
        MAX(u.id) DESC
        <if test="pageNum>=0 and numPerPage>0">
            LIMIT #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="listUserByIds" parameterType="map" resultType="com.pcloud.book.group.vo.UserBookInfoVO">
        SELECT
        s.wx_user_id wxUserId,
        s.alt_id altId
        FROM
        book_group_cipher_user s
        WHERE s.id IN
        <foreach collection="idList" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
        ORDER BY
        s.create_time DESC,
        s.id DESC
    </select>

    <select id="getScanBookInfoByUserNew" parameterType="map" resultType="com.pcloud.book.group.vo.UserBookInfoItemVO">
        SELECT
        u.book_group_id bookGroupId,
        b.BOOK_NAME bookName,
        a.GRA_LABEL_ID graLabelId,
        a.SUB_LABEL_ID subLabelId,
        a.VER_LABEL_ID verLabelId,
        a.AREA_LABEL_ID areaLabelId
        FROM
        book_group_cipher_user u
        INNER JOIN book_group g ON u.book_group_id = g.id AND g.is_delete=0
        INNER JOIN book_adviser a ON g.book_id = a.BOOK_ID
        AND a.IS_DELETE=0
        AND g.create_user = a.ADVISER_ID
        AND g.channel_id = a.CHANNEL_ID
        INNER JOIN book b ON g.book_id = b.BOOK_ID AND b.IS_DELETE=0
        WHERE
        u.wx_user_id = #{wxUserId}
        AND u.alt_id = #{altId}
        GROUP BY
        u.book_group_id
        ORDER BY
        g.id DESC
    </select>

    <select id="getAltIdListByUserId" resultType="string" parameterType="string">
        SELECT DISTINCT
        alt_id
        FROM
        book_group_cipher_user
        WHERE
        wx_user_id = #{wxUserId}
    </select>

</mapper>