<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.pcloud.book.applet.dao.impl.AppletUserBookcaseDaoImpl" >
  <resultMap id="BaseResultMap" type="com.pcloud.book.applet.entity.AppletUserBookcase" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="wechat_user_id" property="wechatUserId" jdbcType="BIGINT" />
    <result column="book_id" property="bookId" jdbcType="BIGINT" />
    <result column="channel_id" property="channelId" jdbcType="BIGINT" />
    <result column="adviser_id" property="adviserId" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="read_type" property="readType" jdbcType="TINYINT" />
    <result column="rights_setting_id" property="rightsSettingId" jdbcType="BIGINT" />
    <result column="book_group_id" property="bookGroupId" jdbcType="BIGINT" />
    <result column="official_accounts_id" property="officialAccountsId" jdbcType="BIGINT" />
  </resultMap>

  <sql id="Base_Column_List" >
    id, wechat_user_id, book_id, channel_id, adviser_id, create_time,read_type, rights_setting_id, official_accounts_id
  </sql>


    <sql id="Base_Column_List_own" >
    a.id, a.wechat_user_id, a.book_id, a.channel_id, a.adviser_id, a.create_time,read_type, a.rights_setting_id
  </sql>

  <insert id="insert" parameterType="com.pcloud.book.applet.entity.AppletUserBookcase" useGeneratedKeys="true" keyProperty="id">
    insert into applet_user_bookcase (
      wechat_user_id, book_id, channel_id, adviser_id, create_time, read_type, rights_setting_id, official_accounts_id
      )
    values (
      #{wechatUserId}, #{bookId}, #{channelId}, #{adviserId}, NOW(), #{readType}, #{rightsSettingId}, #{officialAccountsId}
      )
    on duplicate key
    update
    create_time = now()
  </insert>


    <insert id="batchInsert" parameterType="com.pcloud.book.applet.entity.AppletUserBookcase" useGeneratedKeys="true" keyProperty="id">
    insert into applet_user_bookcase (
      wechat_user_id, book_id, channel_id, adviser_id, create_time, read_type, rights_setting_id, official_accounts_id
      )
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.wechatUserId}, #{item.bookId}, #{item.channelId}, #{item.adviserId}, NOW(), #{item.readType}, #{item.rightsSettingId},
        #{item.officialAccountsId}
        )
    </foreach>
    on duplicate key
    update
    create_time = now()
  </insert>

  <select id="listByWechatUserId" parameterType="map" resultType="com.pcloud.book.applet.dto.AppletUserBookcaseDTO">
    SELECT
        c.id id,
        c.book_id bookId,
        c.channel_id channelId,
        c.adviser_id adviserId,
        b.BOOK_NAME bookName,
        b.COVER_IMG coverImg,
        c.create_time createTime,
        b.AUTHOR author,
        b.PUBLISH publish,
        a.SECOND_TEMPLET_ID secondTempletId,
        a.third_TEMPLET_ID thirdTempletId,
        a.TEMPLET_ID templetId,
        b.ISBN isbn,
        a.GRA_LABEL_ID gradeLabelId,
        a.SUB_LABEL_ID subjectLabelId,
        a.vol_label_id volLabelId,
        a.VER_LABEL_ID verLabelId,
        a.AREA_LABEL_ID areaLabelId
    FROM
        applet_user_bookcase c
    LEFT JOIN BOOK_ADVISER a ON c.book_id = a.BOOK_ID
    AND c.adviser_id = a.ADVISER_ID
    AND c.channel_id = a.CHANNEL_ID
    AND a.IS_DELETE = 0
    INNER JOIN BOOK b ON c.BOOK_ID = b.BOOK_ID
    AND b.IS_DELETE = 0
    WHERE
        c.wechat_user_id IN
        <foreach collection="wechatUserIdList" separator="," item="item" close=")" open="(" index="index">
            ${item}
        </foreach>
    GROUP BY
        c.book_id,
        a.ADVISER_ID,
        c.channel_id
    ORDER BY
        c.create_time DESC
  </select>

  <insert id="insertClickRecord" parameterType="com.pcloud.book.applet.entity.AppletUserClickRecord">
    insert into applet_user_click_record (
      id,wechat_user_id, book_id, from_id, from_type, channel_id, adviser_id, create_time
      )
    values (
      #{id}, #{wechatUserId}, #{bookId}, #{fromId}, #{fromType}, #{channelId}, #{adviserId}, NOW()
      )
  </insert>

  <select id="getUserClickServerCount" parameterType="map" resultType="Integer">
        SELECT COUNT(DISTINCT from_id)
        FROM applet_user_click_record
        WHERE wechat_user_id=#{wechatUserId}
        AND book_id = #{bookId}
        AND channel_id=#{channelId}
        AND adviser_id=#{adviserId}
        AND from_id in
        <foreach collection="list" index="i" item="item" open="(" separator="," close=")">
              ${item}
        </foreach>
        AND from_type = #{fromType}
  </select>

    <select id="getMinClickTime" resultType="string" parameterType="map">
        SELECT
        DATE_FORMAT(MIN(create_time),'%Y-%m-%d')
        FROM applet_user_click_record
        WHERE wechat_user_id=#{wechatUserId}
        AND book_id = #{bookId}
        AND channel_id=#{channelId}
        AND adviser_id=#{adviserId}
        AND from_id in
        <foreach collection="list" index="i" item="item" open="(" separator="," close=")">
            ${item}
        </foreach>
        AND from_type = #{fromType}
    </select>

    <delete id="deleteByBookIdUserId" parameterType="com.pcloud.book.applet.entity.AppletUserBookcase">
        DELETE FROM applet_user_bookcase
        WHERE wechat_user_id=#{wechatUserId}
        AND book_id=#{bookId}
        AND channel_id=#{channelId}
        AND adviser_id=#{adviserId}
    </delete>

    <select id="getUserLastGroupBookRe" parameterType="long" resultType="com.pcloud.book.applet.dto.UserLastBookReDTO">
        SELECT
            t.book_id bookId,
            t.adviser_id adviserId,
            t.channel_id channelId,
            t.read_type readType,
            1 bookGroup
        FROM
            applet_user_bookcase t
        INNER JOIN book_adviser a ON t.book_id = a.BOOK_ID
        AND t.channel_id = a.CHANNEL_ID
        AND t.adviser_id = a.ADVISER_ID
        AND a.IS_DELETE = 0
        LEFT JOIN book_group t1 ON t.book_id = t1.book_id
        AND t1.is_delete = 0
        WHERE
            t.channel_id >= 1
        AND t.adviser_id >= 1
        AND t.wechat_user_id = #{wechatUserId}
        AND (
            a.is_open_robot_process = 1
            OR t1.join_group_type = 4
        )
        ORDER BY
            t.create_time DESC,
            t1.id DESC
        LIMIT 1
    </select>

    <select id="getUserLastRe" parameterType="map" resultType="com.pcloud.book.applet.dto.UserLastBookReDTO" >
        SELECT
            t.book_id bookId,
            t.adviser_id adviserId,
            t.channel_id channelId,
            p.open_rays openRays
        FROM
            applet_user_bookcase t
        INNER JOIN resource_page p ON t.book_id = p.book_id
        AND p.channel_id = t.channel_id
        AND p.create_user = t.adviser_id
        WHERE
            t.channel_id >= 1
        AND t.adviser_id >= 1
        AND (
        p.open_rays = 1
        <if test="bookId != null">
            OR t.book_id = #{bookId}
        </if>
        <if test="adviserId != null">
            OR t.adviser_id = #{adviserId}
        </if>
        )
        AND t.wechat_user_id IN
        <foreach collection="wechatUserIdList" separator="," item="item" close=")" open="(" index="index">
            #{item}
        </foreach>
        ORDER BY
            t.create_time DESC
        LIMIT 1
    </select>

    <delete id="deleteByIds" parameterType="list">
        DELETE FROM applet_user_bookcase
        WHERE id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            ${item}
        </foreach>
    </delete>

    <update id="updateUserReadType" parameterType="com.pcloud.book.applet.entity.AppletUserBookcase">
      update
      applet_user_bookcase
      set
      read_type = #{readType}
      WHERE
      wechat_user_id=#{wechatUserId}
      AND book_id=#{bookId}
      AND channel_id=#{channelId}
      AND adviser_id=#{adviserId}
    </update>

  <select id="getUserReadType" parameterType="map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from
    applet_user_bookcase
    where
    wechat_user_id IN
      <foreach collection="wechatUserIdList" separator="," item="item" close=")" open="(" index="index">
          ${item}
      </foreach>
    AND book_id=#{bookId}
    AND channel_id=#{channelId}
    AND adviser_id=#{adviserId}
    limit 1
  </select>

    <select id="getListByUserId" parameterType="long" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List_own"/>, t1.id book_group_id
        FROM
        applet_user_bookcase a
        left JOIN book_group t1 ON a.book_id = t1.book_id
        AND t1.join_group_type = 4
        AND t1.is_delete = 0
        WHERE
        wechat_user_id = #{wechatUserId}
        group by a.book_id, a.channel_id, a.adviser_id
        ORDER BY
        create_time DESC, book_group_id desc
    </select>

    <select id="getBookcaseIdListByUser" resultType="long" parameterType="list">
        SELECT DISTINCT id
        FROM applet_user_bookcase
        WHERE wechat_user_id IN
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

    <update id="updateRightsSettingId" parameterType="com.pcloud.book.applet.entity.AppletUserBookcase">
      update
      applet_user_bookcase
      set
      rights_setting_id = #{rightsSettingId}
      WHERE
      book_id=#{bookId}
      AND channel_id=#{channelId}
      AND adviser_id=#{adviserId}
    </update>

    <select id="getBookUserCountList" resultType="com.pcloud.book.applet.dto.AppletUserBookcaseDTO" parameterType="map">
     SELECT book_id bookId, adviser_id adviserId, channel_id channelId, COUNT(1) bookUserCount
      FROM applet_user_bookcase
      WHERE  1=1
      <if test="bookIds != null and bookIds.size>0">
          AND book_id in
          <foreach collection="bookIds" open="(" close=")" separator="," index="index" item="item">
              #{item}
          </foreach>
      </if>
     GROUP BY book_id, adviser_id, channel_id
    </select>

    <select id="getBookIdsByIds" resultType="long" parameterType="map">
        SELECT DISTINCT book_id
        FROM applet_user_bookcase
        WHERE id in
        <foreach collection="ids" open="(" close=")" separator="," index="index" item="item">
            #{item}
        </foreach>
    </select>

    <select id="getUserBookInfoByWechatUserId" parameterType="long" resultType="com.pcloud.book.applet.dto.AppletUserBookcaseDTO">
    SELECT
        c.id id,
        c.book_id bookId,
        c.channel_id channelId,
        c.adviser_id adviserId,
        b.BOOK_NAME bookName,
        b.COVER_IMG coverImg,
        c.create_time createTime,
		b.AUTHOR author,
		b.PUBLISH publish,
		a.SECOND_TEMPLET_ID secondTempletId,
		a.third_TEMPLET_ID thirdTempletId,
		a.TEMPLET_ID templetId,
		d.join_group_type joinGroupType,
		d.id bookGroupId,
		b.ISBN isbn,
		a.GRA_LABEL_ID gradeLabelId,
        a.SUB_LABEL_ID subjectLabelId,
        a.vol_label_id volLabelId,
        a.VER_LABEL_ID verLabelId,
        a.AREA_LABEL_ID areaLabelId,
        IF(d.join_group_type=1 OR d.related_book_group_id>0,1,0) hasGroup,
        c.rights_setting_id rightsSettingId,
        r.count rightsSettingCount,
        d.related_book_group_id relatedBookGroupId
    FROM applet_user_bookcase c
    LEFT JOIN BOOK_ADVISER a ON c.book_id=a.BOOK_ID AND c.adviser_id=a.ADVISER_ID
    AND c.channel_id=a.CHANNEL_ID AND a.IS_DELETE = 0
	INNER JOIN BOOK b ON c.BOOK_ID = b.BOOK_ID AND b.IS_DELETE = 0
	LEFT JOIN book_group d ON d.BOOK_ID = a.BOOK_ID and d.create_user=c.adviser_id and d.channel_id=c.channel_id AND d.IS_DELETE = 0
	LEFT JOIN rights_setting r ON c.rights_setting_id = r.id AND d.join_group_type=4
    WHERE  c.book_id = #{bookId} AND c.channel_id = #{channelId} AND c.adviser_id = #{adviserId}
    ORDER BY c.create_time DESC
    LIMIT 1
  </select>

    <select id="getUserReadBook" parameterType="long" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from applet_user_bookcase where wechat_user_id = #{wechatUserId} order by create_time desc limit 1
    </select>

    <select id="getBookClassifyInfo" parameterType="map" resultType="com.pcloud.book.rightsSetting.entity.BaseTempletClassify">
        SELECT b.TEMPLET_ID        firstClassify,
               b.SECOND_TEMPLET_ID secondClassify,
               b.third_TEMPLET_ID thirdClassify,
               b.GRA_LABEL_ID      gradeLabelId,
               b.SUB_LABEL_ID      subjectLabelId,
               b.vol_label_id      volLabelId
        FROM applet_user_bookcase a
                 LEFT JOIN book_adviser b ON a.book_id = b.BOOK_ID
            AND a.channel_id = b.CHANNEL_ID
            AND a.adviser_id = b.ADVISER_ID
        WHERE b.IS_DELETE = 0
          AND a.book_id = #{bookId}
          AND a.channel_id = #{channelId}
          AND a.adviser_id = #{adviserId}
        ORDER BY a.create_time DESC
        LIMIT 1
    </select>

    <select id="getLastReadBookInfo" parameterType="map" resultType="com.pcloud.book.rightsSetting.entity.BaseTempletClassify">
        SELECT b.TEMPLET_ID        firstClassify,
               b.SECOND_TEMPLET_ID secondClassify,
               b.third_TEMPLET_ID thirdClassify,
                b.GRA_LABEL_ID      gradeLabelId,
               b.SUB_LABEL_ID      subjectLabelId,
               b.vol_label_id      volLabelId
        FROM applet_user_bookcase a
                 LEFT JOIN book_adviser b ON a.book_id = b.BOOK_ID
            AND a.channel_id = b.CHANNEL_ID
            AND a.adviser_id = b.ADVISER_ID
        WHERE b.IS_DELETE = 0
          AND a.wechat_user_id = #{wechatUserId}
        ORDER BY a.create_time DESC
        LIMIT 1
    </select>

    <select id="getUserLastGroupBook" parameterType="long" resultType="com.pcloud.book.applet.dto.UserLastBookReDTO">
       SELECT
            book_id bookId,
            adviser_id adviserId,
            channel_id channelId,
            read_type readType
        FROM
            applet_user_bookcase
        WHERE
            channel_id >= 1
        AND adviser_id >= 1
        AND wechat_user_id = #{wechatUserId}
        ORDER BY
            create_time DESC
        LIMIT 1
    </select>

    <select id="getLastReadBookInfoByUsers"  parameterType="list" resultType="com.pcloud.book.applet.dto.BaseTempletClassifyDTO">
        SELECT b.TEMPLET_ID        firstClassify,
               b.SECOND_TEMPLET_ID secondClassify,
               b.third_TEMPLET_ID thirdClassify,
                b.GRA_LABEL_ID      gradeLabelId,
               b.SUB_LABEL_ID      subjectLabelId,
               b.vol_label_id      volLabelId
        FROM applet_user_bookcase a
                 LEFT JOIN book_adviser b ON a.book_id = b.BOOK_ID
            AND a.channel_id = b.CHANNEL_ID
            AND a.adviser_id = b.ADVISER_ID
        WHERE b.IS_DELETE = 0
          AND a.wechat_user_id in
          <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
              #{item}
          </foreach>
        ORDER BY a.create_time DESC
        LIMIT 1
    </select>

    <select id="getSameClassifyUserIds" parameterType="com.pcloud.book.applet.dto.BaseTempletClassifyDTO" resultType="long">
        SELECT
        DISTINCT
	A.wechat_user_id
    FROM
	`applet_user_bookcase` a
    LEFT JOIN book_adviser b ON a.book_id = b.BOOK_ID
    WHERE
	b.IS_DELETE = 0
    AND b.IS_MAIN_EDITOR = 1
    AND b.TEMPLET_ID = #{firstClassify}
    AND b.SECOND_TEMPLET_ID = #{secondClassify}
    AND B.third_templet_id = #{thirdClassify}
    limit 1000
    </select>

    <select id="getLatest10Books4Clock" resultType="com.pcloud.book.book.dto.BookDto" parameterType="list">
        SELECT
            c.book_id bookId,
            c.channel_id channelId,
            c.adviser_id adviserId,
            b.BOOK_NAME bookName,
            b.COVER_IMG coverImg
        FROM
            applet_user_bookcase c
        LEFT JOIN BOOK_ADVISER a ON c.book_id = a.BOOK_ID
        AND c.adviser_id = a.ADVISER_ID
        AND c.channel_id = a.CHANNEL_ID
        AND a.IS_DELETE = 0
        INNER JOIN BOOK b ON c.BOOK_ID = b.BOOK_ID
        AND b.IS_DELETE = 0
        WHERE
            c.wechat_user_id IN
            <foreach collection="list" separator="," item="item" close=")" open="(" index="index">
                #{item}
            </foreach>
        GROUP BY
            c.book_id,
            a.ADVISER_ID,
            c.channel_id
        ORDER BY
            c.create_time DESC
        LIMIT 10
    </select>

</mapper>