<?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.copyright.dao.impl.BookAuthInfoDaoImpl">

    <resultMap id="BookAuthInfoMap" type="bookAuthInfoVO">
        <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="ADVISER_ID" property="adviserId" jdbcType="BIGINT" />
        <result column="price" property="price" jdbcType="DOUBLE" />
        <result column="CODE_USE_COUNT" property="codeUseCount" jdbcType="TINYINT" />
        <result column="BUY_URL" property="buyUrl" jdbcType="VARCHAR" />
        <result column="LOCATION_DESC" property="locationDesc" jdbcType="VARCHAR" />
        <result column="BOOK_STATUS" property="bookStatus" jdbcType="TINYINT" />
        <result column="check_type" property="checkType" jdbcType="TINYINT" />
    </resultMap>

    <insert id="insert" useGeneratedKeys="true" parameterType="bookAuthInfo">
      INSERT INTO BOOK_AUTH_INFO(
			BOOK_ID, CHANNEL_ID,ADVISER_ID,PRICE,CODE_USE_COUNT,BUY_URL,CHECK_TYPE, CREATED_USER, CREATED_DATE,LAST_MODIFIED_DATE,OPEN_TIME,
			is_paper_book, is_group_book
		)
	    VALUES 
       (#{bookId,jdbcType=BIGINT}, #{channelId,jdbcType=BIGINT}, #{adviserId,jdbcType=BIGINT},
		 #{price}, #{codeUseCount,jdbcType=BIGINT}, #{buyUrl}, #{checkType},
        #{adviserId,jdbcType=BIGINT}, NOW(), NOW(), NOW(),
        #{isPaperBook}, #{isGroupBook})
    </insert>

    <select id="getInfoByBook" resultMap="BookAuthInfoMap" parameterType="map">
        SELECT
           ID, BOOK_ID, CHANNEL_ID,ADVISER_ID,PRICE,CODE_USE_COUNT,BUY_URL,CHECK_TYPE,BOOK_STATUS,LOCATION_DESC
        FROM
           BOOK_AUTH_INFO
        WHERE
           BOOK_ID = #{bookId, jdbcType=BIGINT}
        AND
           CHANNEL_ID = #{channelId, jdbcType=BIGINT}
        AND
           ADVISER_ID = #{adviserId, jdbcType=BIGINT}
        <if test="isPaperBook != null">
           AND
            is_paper_book = #{isPaperBook}
        </if>
        <if test="isGroupBook!=null">
           AND
            is_group_book = #{isGroupBook}
        </if>
    </select>

    <select id="getIsHaveAuthBook" resultType="Boolean" parameterType="Long">
        SELECT
           count(1)
        FROM
           BOOK_AUTH_INFO
        WHERE
           BOOK_STATUS in (0,1)
        AND
           ADVISER_ID = #{_parameter, jdbcType=BIGINT}
    </select>

    <!-- 获取设置过正版授权的图书 -->
    <select id="listHaveSetAuthBook" resultType="haveSetAuthBook" parameterType="map">
        SELECT
        I.ID,A.BOOK_ID as bookId,
        A.CHANNEL_ID as channelId,
        I.price,
        A.ADVISER_ID as adviserId,
        B.ISBN,
        B.BOOK_NAME as bookName,
        B.COVER_IMG coverImg,
        B.SERIAL_NUMBER serialNumber,
        A.IS_MAIN_EDITOR isMainEditor,
        I.book_status bookStatus
        FROM
        BOOK_AUTH_INFO I
        INNER JOIN BOOK_ADVISER A ON A.BOOK_ID = I.BOOK_ID AND A.CHANNEL_ID = I.CHANNEL_ID AND A.CREATED_USER = I.ADVISER_ID AND  A.IS_DELETE = 0
        INNER JOIN BOOK B ON I.BOOK_ID = B.BOOK_ID AND I.ADVISER_ID = #{adviserId} AND  B.IS_DELETE = 0
        <if test="isFundSupport != null">
        LEFT JOIN BOOK_FUND BF  ON  BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
        </if>
        WHERE I.is_paper_book = 1
        <if test="typeCode!=null">
            AND
            B.TYPE_CODE = #{typeCode}
        </if>
        <if test="bookStatus!=null">
            AND
            I.book_status = #{bookStatus}
        </if>
        <if test="channelId!=null">
            AND
            A.CHANNEL_ID = #{channelId}
        </if>
        <if test="name != null">
            AND
            ((B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%'))
            <if test="nameList != null and nameList.size > 0">
                <foreach collection="nameList" close=" " separator=" " open=" " item="item">
                    OR
                    (B.BOOK_NAME LIKE CONCAT('%', '${item}', '%') OR  B.ISBN LIKE CONCAT('${item}','%'))
                </foreach>
            </if>
            )
        </if>
        <if test="isMainEditor != null">
            AND
            A.IS_MAIN_EDITOR = 1
        </if>
        <if test="isFundSupport != null">
            AND
            BF.BOOK_FUND_ID IS NOT NULL
        </if>
        <if test="templetId != null">
            AND A.TEMPLET_ID = #{templetId}
        </if>
        <if test="secondTempletIds!=null and secondTempletIds.size()>0">
            AND
            A.SECOND_TEMPLET_ID in
            <foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        GROUP BY A.BOOK_ID, A.CHANNEL_ID
        ORDER BY
        I.LAST_MODIFIED_DATE DESC
    </select>

    <!-- 获取设置过正版授权的社群书 -->
    <select id="listHaveSetAuthGroupBook" resultType="haveSetAuthBook" parameterType="map">
        SELECT
            I.id,
            I.book_id AS bookId,
            I.channel_id AS channelId,
            I.price,
            I.adviser_id AS adviserId,
            I.book_status AS bookStatus,
            B.isbn,
            B.book_name AS bookName,
            B.cover_img AS coverImg,
            B.serial_number AS serialNumber
        FROM
            book_auth_info I
        INNER JOIN book_adviser A ON A.book_id = I.book_id
            AND A.channel_id = I.channel_id
            AND A.created_user = I.adviser_id
            AND A.is_delete = 0
        INNER JOIN book_group G ON G.book_id = I.book_id
            AND G.channel_id = I.channel_id
            AND G.create_user = I.adviser_id
            AND G.is_delete = 0
        INNER JOIN book B ON I.book_id = B.book_id
            AND B.is_delete = 0
        WHERE I.is_group_book = 1
        AND I.adviser_id = #{adviserId}
        <if test="typeCode!=null">
            AND
            B.type_code = #{typeCode}
        </if>
        <if test="bookStatus!=null">
            AND
            I.book_status = #{bookStatus}
        </if>
        <if test="channelId!=null">
            AND
            I.channel_id = #{channelId}
        </if>
        <if test="name != null">
            AND
            (B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%'))
        </if>
        <if test="templetId != null">
            AND
            A.TEMPLET_ID = #{templetId}
        </if>
        <if test="secondTempletIds != null and secondTempletIds.size() > 0">
            AND
            A.SECOND_TEMPLET_ID in
            <foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        GROUP BY A.BOOK_ID, A.CHANNEL_ID
        ORDER BY
              I.LAST_MODIFIED_DATE DESC
    </select>

    <update id="updateBookStatus" parameterType="map" flushCache="true">
        UPDATE BOOK_AUTH_INFO
        SET
            BOOK_STATUS = #{bookStatus},
            <if test="openTime!=null">
                OPEN_TIME = #{openTime},
            </if>
            <if test="closeTime!=null">
                CLOSE_TIME = #{closeTime},
            </if>
            LAST_MODIFIED_DATE = NOW(),
            LAST_MODIFIED_USER = #{adviserId}
        WHERE
        ID = #{id}
    </update>

    <update id="updateLocationDesc" parameterType="bookAuthInfo" flushCache="true">
        UPDATE BOOK_AUTH_INFO
        SET
            LOCATION_DESC = #{locationDesc},
            LAST_MODIFIED_DATE = NOW(),
            LAST_MODIFIED_USER = #{adviserId}
        WHERE
           BOOK_ID = #{bookId}
        AND CHANNEL_ID = #{channelId}
        AND ADVISER_ID = #{adviserId}
        <if test="isPaperBook != null">
            AND
            is_paper_book = #{isPaperBook}
        </if>
        <if test="isGroupBook!=null">
            AND
            is_group_book = #{isGroupBook}
        </if>

    </update>
    <update id="updateBaseInfo" parameterType="bookAuthInfo" flushCache="true">
        UPDATE BOOK_AUTH_INFO
        SET
        <if test="price != null">
            price = #{price},
        </if>
        <if test="codeUseCount != null">
            CODE_USE_COUNT = #{codeUseCount},
        </if>
        <if test="buyUrl != null">
            BUY_URL = #{buyUrl},
        </if>
        <if test="checkType != null">
            CHECK_TYPE = #{checkType},
        </if>
        <if test="lastModifiedUser != null">
            LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT},
        </if>
           LAST_MODIFIED_DATE = NOW()
        WHERE
        ID = #{id}
    </update>

    <select id="getBookAuthCount" resultType="bookAuthInfoCountDTO" parameterType="map">
        SELECT
           BOOK_ID bookId, CHANNEL_ID channelId, ADVISER_ID adviserId, OPEN_TIME openTime, CLOSE_TIME closeTime
        FROM
           BOOK_AUTH_INFO
        WHERE
           BOOK_ID = #{bookId, jdbcType=BIGINT}
        AND
           CHANNEL_ID = #{channelId, jdbcType=BIGINT}
        AND
           ADVISER_ID = #{adviserId, jdbcType=BIGINT}
        AND
           is_paper_book = 1
    </select>

    <select id="getTotalNum" resultType="Integer" parameterType="map">
        SELECT
          COUNT(1)
        FROM
          BOOK_AUTH_INFO
        WHERE
          BOOK_STATUS = 1
        <if test="adviserIds!=null">
            AND ADVISER_ID in
            <foreach collection="adviserIds" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        <if test="removeAdviserIds!=null">
            AND ADVISER_ID not in
            <foreach collection="removeAdviserIds" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
    </select>

</mapper>