<?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.book.dao.impl.BookAdviserDaoImpl">

	<!-- 书籍编辑实体DTO -->
	<resultMap id="bookAdviserMap" type="bookAdviserDto">
		<id column="BOOK_ADVISER_ID" property="bookAdviserId" jdbcType="BIGINT" />
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="ADVISER_ID" property="adviserId" jdbcType="BIGINT" />
		<result column="CHANNEL_ID" property="channelId" jdbcType="BIGINT" />
		<result column="TEMPLET_ID" property="templetId" jdbcType="BIGINT" />
		<result column="CREATED_USER" property="createdUser" jdbcType="BIGINT" />
		<result column="CREATED_DATE" property="createdDate" jdbcType="TIMESTAMP" />
		<result column="VERSION" property="version" jdbcType="BIGINT" />
		<result column="COUNT" property="count" jdbcType="BIGINT" />
		<result column="IS_MAIN_EDITOR" property="isMainEditor" jdbcType="BIT" />
		<result column="IS_DELETE" property="isDelete" jdbcType="BIT" />
		<result column="SECOND_TEMPLET_ID" property="secondTempletId" jdbcType="BIGINT" />
		<result column="GRA_LABEL_ID" property="graLabelId" jdbcType="BIGINT" />
		<result column="SUB_LABEL_ID" property="subLabelId" jdbcType="BIGINT" />
		<result column="VER_LABEL_ID" property="verLabelId" jdbcType="BIGINT" />
		<result column="AREA_LABEL_ID" property="areaLabelId" jdbcType="BIGINT" />
		<result column="is_open_robot_process" property="isOpenRobotProcess" jdbcType="BIT" />
		<result column="vol_label_id" property="volLabelId" jdbcType="BIGINT" />
		<result column="dep_label_id" property="depLabelId" jdbcType="BIGINT" />
		<result column="pro_label_id" property="proLabelId" jdbcType="BIGINT" />
		<result column="pur_label_id" property="purLabelId" jdbcType="BIGINT" />
	</resultMap>
	
	<resultMap id="manageAdviserMap" type="adviserManageDto" >
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="CHANNEL_ID" property="channelId" jdbcType="BIGINT" />
	<collection property="adviserList" column="{channelId=CHANNEL_ID,bookId=bookId}" ofType="bookAdviserDto" select="getAdviserInfoByBookId" />
	
	</resultMap>


	<!-- 查询编辑的书刊信息 -->
	<select id="listBookIdsByAdviser" resultType="long">
		select BOOK_ID from BOOK_ADVISER where IS_DELETE = 0 and ADVISER_ID = #{adviserId} and CREATED_DATE >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
	</select>

		<!-- 新增书籍和编辑关联信息-->
	<insert id="insert" parameterType="bookAdviser" useGeneratedKeys="true" keyProperty="bookAdviserId" flushCache="true">
		INSERT INTO BOOK_ADVISER (
			BOOK_ID, ADVISER_ID, CHANNEL_ID, CREATED_USER, CREATED_DATE,IS_MAIN_EDITOR, TEMPLET_ID, SECOND_TEMPLET_ID,
			GRA_LABEL_ID,SUB_LABEL_ID,VER_LABEL_ID,AREA_LABEL_ID,pro_label_id, dep_label_id,pur_label_id,is_open_robot_process,
		vol_label_id
		)
		VALUES (
			#{bookId, jdbcType=VARCHAR}, #{adviserId, jdbcType=VARCHAR}, #{channelId, jdbcType=VARCHAR}, 
			#{adviserId, jdbcType=BIGINT}, NOW(),#{isMainEditor, jdbcType=BIT},#{templetId, jdbcType=BIGINT},
			#{secondTempletId, jdbcType=BIGINT},#{graLabelId, jdbcType=BIGINT},#{subLabelId, jdbcType=BIGINT},
			#{verLabelId, jdbcType=BIGINT},#{areaLabelId, jdbcType=BIGINT},#{proLabelId,jdbcType=BIGINT},
			#{depLabelId,jdbcType=BIGINT},#{purLabelId,jdbcType=BIGINT},#{isOpenRobotProcess,jdbcType=BIT},
		#{volLabelId}
		)
		ON
			DUPLICATE KEY UPDATE IS_DELETE = 0, IS_MAIN_EDITOR = #{isMainEditor, jdbcType=BIT}
		  <if test="templetId!=null">
			,TEMPLET_ID=#{templetId, jdbcType=BIGINT}
		  </if>
			<if test="secondTempletId!=null">
				,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
			</if>
		<if test="secondTempletId!=null">
			,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
		</if>
		<if test="secondTempletId!=null">
			,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
		</if>
		<if test="secondTempletId!=null">
			,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
		</if>
		<if test="secondTempletId!=null">
			,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
		</if>
	</insert>

	<!-- 批量新增书籍和编辑关联信息-->
	<insert id="batchInsert" parameterType="bookAdviser" useGeneratedKeys="true" keyProperty="bookAdviserId" flushCache="true">
		<foreach collection="list" item="item" index="index" separator=";">
		INSERT INTO BOOK_ADVISER (
			BOOK_ID, ADVISER_ID, CHANNEL_ID, CREATED_USER, CREATED_DATE,IS_MAIN_EDITOR, TEMPLET_ID,SECOND_TEMPLET_ID
		)
		VALUES 
		(
			#{item.bookId, jdbcType=VARCHAR}, #{item.adviserId, jdbcType=VARCHAR}, #{item.channelId, jdbcType=VARCHAR}, 
			#{item.adviserId, jdbcType=BIGINT}, NOW(),#{item.isMainEditor, jdbcType=BIT},#{item.templetId, jdbcType=BIGINT},#{item.secondTempletId, jdbcType=BIGINT}
		) ON DUPLICATE KEY UPDATE IS_DELETE = 0,IS_MAIN_EDITOR=#{item.isMainEditor, jdbcType=BIT}
		<if test="item.templetId!=null">
		   ,TEMPLET_ID=#{item.templetId, jdbcType=BIGINT}
		</if>
		<if test="item.secondTempletId!=null">
		  ,SECOND_TEMPLET_ID=#{item.secondTempletId, jdbcType=BIGINT}
		</if>
		</foreach>
	</insert>
	
	<!-- 删除编辑和书籍的推广信息 -->
	<update id="deleteByBook" parameterType="java.lang.Long" flushCache="true">
		UPDATE  
			BOOK_ADVISER
		SET
		    IS_DELETE = 1,
		    LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</update>
	
	<!-- 批量删除编辑和书籍的推广信息 -->
	<update id="deleteByBooks" parameterType="list" flushCache="true">
		UPDATE  
			BOOK_ADVISER
		SET
		    IS_DELETE = 1,
			LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID IN 
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
	</update>
	
	<!-- 批量获取书籍信息 -->
	<select id="getBase" resultMap="bookAdviserMap" parameterType="map">
	   SELECT 
			BOOK_ADVISER_ID, BOOK_ID, TEMPLET_ID, CHANNEL_ID, ADVISER_ID, IS_DELETE,
			TEMPLET_ID,SECOND_TEMPLET_ID,
			GRA_LABEL_ID,SUB_LABEL_ID,VER_LABEL_ID,AREA_LABEL_ID,is_open_robot_process,vol_label_id
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			ADVISER_ID = #{adviserId}
	</select>

	<select id="getByAdviser" resultMap="bookAdviserMap" parameterType="bookAdviser">
		SELECT
		ba.BOOK_ADVISER_ID, ba.BOOK_ID, ba.CHANNEL_ID, ba.ADVISER_ID, ba.IS_DELETE
		FROM
		BOOK_ADVISER ba
		LEFT JOIN BOOK b on b.BOOK_ID = ba.BOOK_ID
		WHERE
		ba.BOOK_ID = #{bookId}
		AND
		ba.CHANNEL_ID = #{channelId}
		AND
		ba.ADVISER_ID = #{adviserId}
		AND
		ba.IS_DELETE = 0
		<if test="serialNumber != null  and serialNumber != ''">
			AND b.SERIAL_NUMBER = #{serialNumber}
		</if>
		ORDER BY ba.CREATED_DATE
		LIMIT 1
	</select>

	
	<!-- 获取书籍信息 -->
	<select id="getByAdviserContainsDelete" resultMap="bookAdviserMap" parameterType="bookAdviser">
	   SELECT 
			ba.BOOK_ADVISER_ID, ba.BOOK_ID, ba.CHANNEL_ID, ba.ADVISER_ID, ba.IS_DELETE
		FROM 
			BOOK_ADVISER ba
		WHERE
			ba.BOOK_ID = #{bookId}
		AND
			ba.CHANNEL_ID = #{channelId}
		AND
			ba.ADVISER_ID = #{adviserId}
		ORDER BY ba.CREATED_DATE
        LIMIT 1
	</select>
	
	<!-- 统计编辑推广的图书总数 -->
	<select id="getCountByAdvisers" resultMap="bookAdviserMap" parameterType="list">
		SELECT 
			ADVISER_ID, COUNT(1) COUNT
		FROM 
			BOOK_ADVISER
		WHERE
			ADVISER_ID IN
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND 
			IS_DELETE = 0 
		GROUP BY 
			ADVISER_ID
	</select>
	
		<!-- 获取配置过书籍的编辑top1 -->
	<select id="getAdviserTop1" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND 
			IS_DELETE = 0
		LIMIT 1
	</select>
	
	<!-- 获取书籍信息 -->
	<!--<select id="getByChannel" resultMap="bookAdviserMap" parameterType="bookAdviser">-->
		<!--SELECT -->
			<!--BOOK_ADVISER_ID, BOOK_ID, CHANNEL_ID, ADVISER_ID-->
		<!--FROM -->
			<!--BOOK_ADVISER -->
		<!--WHERE-->
			<!--BOOK_ID = #{bookId}-->
		<!--AND-->
			<!--CHANNEL_ID = #{channelId}-->
		<!--AND-->
			<!--IS_DELETE = 0-->
		<!--AND -->
			<!--ADVISER_ID != #{adviserId}	      -->
		<!--LIMIT 1-->
	<!--</select>-->


	<select id="getCount4BookAdviser" resultType="Integer" parameterType="Long">
		SELECT
			COUNT(1)
		FROM
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			IS_DELETE = 0
		AND
			IS_MAIN_EDITOR = 1
	</select>
	
	<!-- 根据渠道与书籍标识删除编辑和书籍的推广信息 -->
	<update id="deleteByBookAndChannel" parameterType="map" flushCache="true">
	    UPDATE 
	    	BOOK_ADVISER 
	    SET 
	        IS_DELETE = 1,
		    LAST_MODIFIED_DATE =  NOW()
	    WHERE 
	    	BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND 
		    CHANNEL_ID = #{channelId, jdbcType=BIGINT}
		AND 
		    IS_DELETE = 0
	</update>
	
	<!-- 根据图书信息获取主编辑ID -->
	<select id="getMainAdviserId" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			IS_MAIN_EDITOR = 1
		AND 
			IS_DELETE = 0
		LIMIT 1
	</select>
	
    <!--当前编辑是否是主编辑 -->
	 <select id="isMainAdviser4Book" resultType="Boolean" parameterType="map">
		SELECT 
			count(1)
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			IS_MAIN_EDITOR = 1
		AND 
			ADVISER_ID = #{adviserId}
	</select>
	
	<!-- 根据bookId获取编辑信息 -->
	<select id="getAdviserList" resultMap="manageAdviserMap" parameterType="Long">
		SELECT 
			BOOK_ADVISER_ID, CHANNEL_ID, #{bookId} bookId
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND 
			IS_DELETE = 0
		GROUP BY  CHANNEL_ID
	</select>
	<!-- 根据bookId获取编辑信息 -->
	<select id="getAdviserInfoByBookId" resultMap="bookAdviserMap" >
		SELECT 
			BOOK_ADVISER_ID, BOOK_ID, CHANNEL_ID, ADVISER_ID,IS_MAIN_EDITOR
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
			AND CHANNEL_ID = #{channelId}
			AND IS_DELETE = 0
	</select>
	
	<!--根据bookId获取编辑ID  -->
	<select id="getAdviserIdsByBookId" resultType="Long" parameterType="Long">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND 
			IS_DELETE = 0
	</select>
	
	<!-- 编辑管理  删除副编辑 -->
	<delete id="delViceAdviser" parameterType="bookAdviser"  flushCache="true">
		DELETE FROM 
			BOOK_ADVISER
		WHERE 
			IS_MAIN_EDITOR != 1
			AND	
			<foreach collection="list" item="item" index="index" open="(" separator="OR" close=")">
				BOOK_ID = #{item.bookId, jdbcType=BIGINT}
			AND 
			    CHANNEL_ID = #{item.channelId, jdbcType=BIGINT}
			AND 
				ADVISER_ID = #{item.adviserId, jdbcType=BIGINT}
		</foreach>
		AND 
			IS_DELETE = 0
	</delete>
	
	<!--获取在某个运营平台关联过某本书的所有编辑的ID -->
	<select id="getAdviserIdsByBookAndChannel" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND 
			IS_DELETE = 0
	</select>
	<!-- 根据bookIds查询书刊编辑关联信息 -->
	<select id="getByBookIds" resultMap="bookAdviserMap" parameterType="list" >
		SELECT  BOOK_ADVISER_ID,BOOK_ID, ADVISER_ID, CHANNEL_ID,IS_MAIN_EDITOR
		FROM BOOK_ADVISER 
		WHERE
		  	BOOK_ID IN
			<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND 
			IS_DELETE = 0
		GROUP BY  BOOK_ID
	</select>
	
	<!-- 编辑下的所有书刊的数目 -->
	<select id="getBookCount" parameterType="list" resultType="int">
		SELECT
			COUNT(DISTINCT A.BOOK_ID, A.CHANNEL_ID)
		FROM
			BOOK_ADVISER A
        INNER JOIN
        BOOK B
        ON
        A.BOOK_ID = B.BOOK_ID  AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
        AND
        ADVISER_ID IN
        <foreach collection="list" index="i" item="item" open="(" separator="," close=")">
            ${item}
        </foreach>
        AND A.IS_MAIN_EDITOR = 1
	</select>
	
	<!-- 修改书籍模板信息-->
	<update id="updateBookTemplet" parameterType="bookAdviser" flushCache="true">
		UPDATE 
			BOOK_ADVISER
		SET
			TEMPLET_ID = #{templetId, jdbcType=BIGINT}
			<if test="secondTempletId!=null">
				,SECOND_TEMPLET_ID = #{secondTempletId, jdbcType=BIGINT}
			</if>
			<if test="graLabelId!=null">
				,GRA_LABEL_ID = #{graLabelId, jdbcType=BIGINT}
			</if>
			<if test="subLabelId!=null">
				,SUB_LABEL_ID = #{subLabelId, jdbcType=BIGINT}
			</if>
			<if test="verLabelId!=null">
				,VER_LABEL_ID = #{verLabelId, jdbcType=BIGINT}
			</if>
			<if test="areaLabelId!=null">
				,AREA_LABEL_ID = #{areaLabelId, jdbcType=BIGINT}
			</if>
			<if test="proLabelId!=null">
				,pro_label_id = #{proLabelId, jdbcType=BIGINT}
			</if>
			<if test="depLabelId!=null">
				,dep_label_id = #{depLabelId, jdbcType=BIGINT}
			</if>
			<if test="purLabelId!=null">
				,pur_label_id = #{purLabelId, jdbcType=BIGINT}
			</if>
			<if test="volLabelId!=null">
				,vol_label_id = #{volLabelId, jdbcType=BIGINT}
			</if>
            , LAST_MODIFIED_DATE =  NOW()
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND 
			ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		AND 
			CHANNEL_ID = #{channelId, jdbcType=BIGINT}
	</update>	
	
	<!-- 代理每日新增书刊-->
	<select id="listBookCountByDate4Agent" resultType="bookCountDto" parameterType="map">
	    SELECT
			DATE(CREATED_DATE) date,
			COUNT(DISTINCT BOOK_ID) as bookCount
		FROM
			`BOOK_ADVISER` ba
		WHERE
			ba.IS_DELETE = 0
		AND	ba.ADVISER_ID IN 
		 <foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")"> 
		  		${item} 
		 </foreach>
		AND DATE(CREATED_DATE) BETWEEN #{startDate} AND #{endDate}
		AND BOOK_ID NOT IN (
			SELECT DISTINCT
				BOOK_ID
			FROM
				BOOK_ADVISER
			WHERE
		<![CDATA[	DATE(CREATED_DATE) < DATE(ba.CREATED_DATE)  ]]> 
			AND ADVISER_ID IN 
		 <foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")"> 
		  		${item} 
		 </foreach>
		 AND 
			IS_DELETE = 0
		)
		GROUP BY
			DATE(CREATED_DATE)
	</select>
	
	<!-- 恢复图书 -->
	<update id="updateBook4Adviser" parameterType="map" flushCache="true">
		UPDATE
			BOOK_ADVISER
		SET
			IS_DELETE = #{status, jdbcType=BIGINT}
		<if test="isMainAdviser != null">	
			,IS_MAIN_EDITOR = #{isMainAdviser}
		</if>
		<if test="templetId != null">	
			,TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletId != null">
			,SECOND_TEMPLET_ID = #{secondTempletId}
		</if>
		<if test="graLabelId!=null">
			,GRA_LABEL_ID = #{graLabelId, jdbcType=BIGINT}
		</if>
		<if test="subLabelId!=null">
			,SUB_LABEL_ID = #{subLabelId, jdbcType=BIGINT}
		</if>
		<if test="verLabelId!=null">
			,VER_LABEL_ID = #{verLabelId, jdbcType=BIGINT}
		</if>
		<if test="areaLabelId!=null">
			,AREA_LABEL_ID = #{areaLabelId, jdbcType=BIGINT}
		</if>
		<if test="proLabelId!=null">
			,pro_label_id = #{proLabelId, jdbcType=BIGINT}
		</if>
		<if test="depLabelId!=null">
			,dep_label_id = #{depLabelId, jdbcType=BIGINT}
		</if>
		<if test="purLabelId!=null">
			,pur_label_id = #{purLabelId, jdbcType=BIGINT}
		</if>
		<if test="volLabelId!=null">
			,vol_label_id = #{volLabelId, jdbcType=BIGINT}
		</if>
			,LAST_MODIFIED_DATE =  NOW()
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		<if test="adviserId != null">
		AND 
			ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		</if>
		AND 
			CHANNEL_ID = #{channelId, jdbcType=BIGINT}
	</update>
	
	<!-- 获取编辑的图书总数 -->
	<select id="getAdviserBookCount" resultType="int" parameterType="long">
		SELECT
			COUNT(b.BOOK_ID)
		FROM
			book b,
			book_adviser ba
		WHERE
			ba.ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		AND
			ba.IS_DELETE = 0 AND b.IS_DELETE = 0
		AND 
			ba.BOOK_ID = b.BOOK_ID	
	</select>
	
	<!-- 获取被诉方信息(即与该书关联的所有编辑) -->
	<select id="getAdvisers" resultMap="bookAdviserMap" parameterType="Long">
		SELECT
			ADVISER_ID,  BOOK_ID, CHANNEL_ID, IS_MAIN_EDITOR, CREATED_DATE
		FROM
			book_adviser
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND
			IS_DELETE = 0
	</select>


	<select id="listAdviserBookInfo" resultType="AdviserBookInfoDTO" parameterType="aviserBookInfoParam">
		SELECT
			ba.BOOK_ID bookId,
			b.BOOK_NAME bookName,
			b.cover_img coverImg,
			b.type_code typeCode,
			b.isbn,
			b.serial_number serialNumber,
			ba.adviser_id adviserId,
			ba.channel_id channelId,
			ba.created_date createdDate
		FROM
			book_adviser ba join book b on ba.book_id = b.book_id and  ba.is_delete = 0 and b.is_delete = 0
		WHERE
		  ba.BOOK_ID in
		<foreach collection = "bookIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		<if test="adviserIds!=null">
		AND
			ADVISER_ID in
		<foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		</if>
		<if test="channelIds!=null">
		AND
			CHANNEL_ID in
		<foreach collection = "channelIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		</if>
	</select>

	<select id="getBookCountByAdviserId" parameterType="bookCountByAdvisersDto" resultType="bookCountAndAdviserIdDTO">
		SELECT adviser_id adviserId, count(distinct (book_id)) bookCount
		FROM book_adviser
		WHERE adviser_id IN
		<foreach collection="adviserIds" item="adviserId" open="(" close=")" separator=",">
			#{adviserId}
		</foreach>
		<if test="startTime != null">
			AND CREATED_DATE &gt;= DATE_FORMAT(#{startTime}, '%Y-%m-%d 00:00:00')
		</if>
		<if test="endTime != null">
			AND CREATED_DATE &lt;= DATE_FORMAT(#{endTime}, '%Y-%m-%d 23:59:59')
		</if>
		GROUP BY adviser_id
	</select>

	<!--刷数据使用，请勿使用-->
	<update id="updateBookBySecondTempletId" parameterType="map">
		UPDATE
			book_adviser
	  	SET
	  		<if test="secondTempletId!=null">
		  		SECOND_TEMPLET_ID = #{secondTempletId},
			</if>
		  	TEMPLET_ID = #{parentId}
	  	WHERE
		  	TEMPLET_ID = #{originTempletId}
	</update>

	<select id="listAdviserBook4Erp" parameterType="map" resultType="erpAdviserBookVO">
        SELECT
        ba.BOOK_ID bookId,
        ba.ADVISER_ID adviserId,
        ba.channel_id channelId,
        b.BOOK_NAME bookName,
        b.COVER_IMG coverImg,
        b.ISBN isbn,
        CONCAT('BK', ba.BOOK_ID) uniqueNumber,
        ba.BOOK_ADVISER_ID bookAdviserId,
        b.SERIAL_NUMBER serialNumber
        FROM
        `book_adviser` ba
        LEFT JOIN book b ON ba.BOOK_ID = b.BOOK_ID
        WHERE
        ba.IS_DELETE = 0
        AND b.IS_DELETE = 0
        <if test="isbn != null">
            AND (b.ISBN =#{isbn} or b.BOOK_NAME LIKE CONCAT('%', #{isbn}, '%'))
        </if>
        <if test="uniqueNumber != null">
            AND CONCAT('BK', ba.BOOK_ID) LIKE CONCAT('%', #{uniqueNumber}, '%')
        </if>
        <if test="adviserIds != null and adviserIds.size() > 0">
            AND ba.ADVISER_ID IN
            <foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        GROUP BY
        ba.BOOK_ADVISER_ID
        ORDER BY
        ba.CREATED_DATE DESC
	</select>

	<select id="listAdviserBook4Pcloud" parameterType="map" resultType="com.pcloud.book.book.vo.PcloudAdviserBookVO">
		SELECT
		ba.BOOK_ID bookId,
		ba.ADVISER_ID adviserId,
		b.BOOK_NAME bookName,
		b.COVER_IMG coverImg,
		b.ISBN isbn,
		ba.BOOK_ADVISER_ID bookAdviserId
		FROM
		`book_adviser` ba
		LEFT JOIN book b ON ba.BOOK_ID = b.BOOK_ID
		WHERE
		ba.IS_DELETE = 0
		<if test="keyword != null">
			AND (
			b.ISBN =#{keyword}
			or b.BOOK_NAME LIKE CONCAT('%', #{keyword}, '%')
			or b.BOOK_ID LIKE CONCAT('%', #{keyword}, '%')
			or ba.BOOK_ADVISER_ID LIKE CONCAT('%', #{keyword}, '%')
			)
		</if>
		AND b.IS_DELETE = 0
		GROUP BY
		ba.BOOK_ADVISER_ID
		ORDER BY
		ba.CREATED_DATE DESC
	</select>

    <!-- 设置书刊是否已下印状态 -->
    <update id="setIsPrint" parameterType="bookAdviser">
        update
            book_adviser
        set
            IS_PRINT = #{isPrint}
        where
            BOOK_ID = #{bookId}
        <if test="channelId != null">
            and CHANNEL_ID = #{channelId}
        </if>
    </update>

	<!--根据模板id查询bookId集合-->
	<select id="getBookIdsByTempletId" parameterType="map">
		select distinct book_id
		from  book_adviser
		WHERE
		TEMPLET_ID = #{templetId}
	</select>

	<select id="getOneMainBook" parameterType="long" resultMap="bookAdviserMap">
		SELECT
		BOOK_ADVISER_ID, BOOK_ID, TEMPLET_ID, CHANNEL_ID, ADVISER_ID, IS_DELETE,
			TEMPLET_ID,SECOND_TEMPLET_ID,
			GRA_LABEL_ID,SUB_LABEL_ID,VER_LABEL_ID,AREA_LABEL_ID,vol_label_id
		FROM book_adviser
		WHERE BOOK_ID=#{bookId}
		AND IS_MAIN_EDITOR=1 AND IS_DELETE=0
		LIMIT 1
	</select>

	<select id="getByBookGroupId" parameterType="long" resultMap="bookAdviserMap">
		SELECT
			a.BOOK_ADVISER_ID, a.BOOK_ID, a.TEMPLET_ID, a.CHANNEL_ID, a.ADVISER_ID, a.IS_DELETE,
			a.TEMPLET_ID,a.SECOND_TEMPLET_ID,
			a.GRA_LABEL_ID,a.SUB_LABEL_ID,a.VER_LABEL_ID,a.AREA_LABEL_ID,a.vol_label_id
		FROM
			book_adviser a
			LEFT JOIN book_group  b ON a.ADVISER_ID = b.create_user AND a.CHANNEL_ID = b.channel_id AND a.BOOK_ID = b.book_id
		WHERE
			b.id = #{bookGroupId}
		LIMIT 1
	</select>

	<select id="getBookListForAdvertising" parameterType="map" resultType="com.pcloud.book.advertising.dto.BookListForAdvertisingDTO">
		SELECT
			t.BOOK_ID bookId,
			t.channel_id channelId,
			t.adviser_id adviserId,
			<if test="is1v1!=null and is1v1">
				tt.id bookGroupId,
			</if>
			t1.BOOK_NAME bookName,
			t1.ISBN,
			t2.advertising_brand_id advertisingBrandId,
			t3.brand_name brandName
		FROM
			book_adviser t
		INNER JOIN book t1 ON t.BOOK_ID = t1.BOOK_ID
		<if test="is1v1!=null and is1v1">
			INNER JOIN book_group tt on t.BOOK_ID = tt.BOOK_ID and tt.join_group_type=2
		</if>
		AND t.IS_DELETE = 0
		AND t1.IS_DELETE = 0
		LEFT JOIN advertising_brand_book t2
		on t2.book_id=t.BOOK_ID
		LEFT JOIN advertising_brand t3 on t2.advertising_brand_id=t3.id
		WHERE
			t.IS_MAIN_EDITOR = 1
		<if test="keyword != null">
			AND (t1.BOOK_NAME LIKE CONCAT('%', #{keyword}, '%') or  t1.ISBN LIKE CONCAT('%', #{keyword},'%'))
		</if>
		<if test="advertisingBrandId!=null">
			and t2.advertising_brand_id=#{advertisingBrandId}
		</if>
		<if test="isPitch!=null and isPitch">
			and t2.advertising_brand_id is null
		</if>
		group by t.BOOK_ID
	</select>

	<select id="getByBookAdviserIds" resultMap="bookAdviserMap" parameterType="list">
		SELECT a.BOOK_ADVISER_ID,a.BOOK_ID, a.ADVISER_ID, a.CHANNEL_ID,a.IS_MAIN_EDITOR
		b.isbn,
		FROM BOOK_ADVISER a
		left book b on a.book_Id = b.book_id
		WHERE
		BOOK_ID IN
		<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND
		IS_DELETE = 0
		GROUP BY BOOK_ID
	</select>

	<select id="getBookCountByTime" parameterType="map" resultType="integer">
		SELECT
		COUNT(DISTINCT A.BOOK_ID, A.CHANNEL_ID)
		FROM
		BOOK_ADVISER A
		INNER JOIN
		BOOK B
		ON
		A.BOOK_ID = B.BOOK_ID  AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		AND
		ADVISER_ID IN
		<foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		<if test="startTime != null">
			AND A.CREATED_DATE >= #{startTime}
		</if>
		<if test="endTime != null">
			AND A.CREATED_DATE &lt;= #{endTime}
		</if>
		AND A.IS_MAIN_EDITOR = 1
	</select>

	<!--走小睿流程的现代纸书的书刊数量-->
	<select id="getRayBookCount" resultType="long" parameterType="map">
		SELECT
		COUNT(1)
		FROM
		(
		SELECT
		a.BOOK_ADVISER_ID
		FROM
		book_adviser a
		LEFT JOIN book_group g ON a.BOOK_ID = g.book_id
		AND a.ADVISER_ID = g.create_user
		AND a.CHANNEL_ID = g.channel_id
		AND g.is_delete = 0
		WHERE
		a.IS_DELETE = 0
		AND g.join_group_type = 4
		<if test="startTime != null and endTime != null">
			AND g.create_time BETWEEN #{startTime} AND #{endTime}
		</if>
		UNION
		(
		SELECT
		BOOK_ADVISER_ID
		FROM
		book_adviser
		WHERE
		is_delete = 0
		AND is_open_robot_process = 1
		<if test="startTime != null and endTime != null">
			AND LAST_MODIFIED_DATE BETWEEN #{startTime} AND #{endTime}
		</if>
		)
		) AS a
	</select>

	<select id="getIsRui" parameterType="map" resultType="integer">
		SELECT
  		IF(is_open_robot_process = 1 OR join_group_type = 4,1,0) isRui
		FROM
	book_adviser a
	LEFT JOIN book_group b ON a.BOOK_ID = b.book_id
	AND a.CHANNEL_ID = b.channel_id
		WHERE
			a.BOOK_ID = #{bookId}
		AND
			a.CHANNEL_ID = #{channelId}
		AND
			a.ADVISER_ID = #{adviserId}
		limit 1
	</select>

	<select id="getBookInfoByBookIds" resultMap="bookAdviserMap">
		SELECT BOOK_ID,ADVISER_ID,CHANNEL_ID,TEMPLET_ID,CREATED_USER,SECOND_TEMPLET_ID,
		GRA_LABEL_ID,SUB_LABEL_ID,VER_LABEL_ID,AREA_LABEL_ID,dep_label_id,pro_label_id,pur_label_id,vol_label_id
		FROM book_adviser
		WHERE is_delete = 0
		AND BOOK_ID IN
		<foreach collection="bookIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND ADVISER_ID IN
		<foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND CHANNEL_ID IN
		<foreach collection="channelIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
	</select>


	<select id="getBookByBookIdAdviserId" parameterType="map" resultMap="bookAdviserMap">
		SELECT
		BOOK_ADVISER_ID, BOOK_ID, TEMPLET_ID, CHANNEL_ID, ADVISER_ID, IS_DELETE,
		TEMPLET_ID,SECOND_TEMPLET_ID,
		GRA_LABEL_ID,SUB_LABEL_ID,VER_LABEL_ID,AREA_LABEL_ID,vol_label_id
		FROM
			book_adviser
		WHERE
			BOOK_ID = #{bookId}
		AND ADVISER_ID = #{adviserId}
		ORDER BY IS_MAIN_EDITOR DESC,IS_DELETE ASC
		LIMIT 1
	</select>
	<select id="getAdviserBookVarietyStats" parameterType="map" resultType="com.pcloud.book.book.dto.BookAdviserDto">
		SELECT
			BOOK_ID AS bookId,
			ADVISER_ID adviserId,
			CHANNEL_ID channelId,
			CREATED_DATE createdDate
		FROM
			book_adviser
		WHERE
			IS_DELETE = 0
		<if test="null != adviserIds and adviserIds.size > 0">
			AND ADVISER_ID IN
			<foreach collection="adviserIds" open="(" item="item" close=")" separator=",">
				#{item}
			</foreach>
		</if>
	</select>

	<select id="getErpPublishNumQueryParams" parameterType="map" resultType="com.pcloud.book.book.dto.ERPPublishNumDTO">
		SELECT
			ADVISER_ID AS adviserId,
			CHANNEL_ID AS channelId,
			BOOK_ID AS bookId,
			CREATED_DATE AS createDate
		FROM
			book_adviser
		WHERE
			IS_DELETE = 0 AND ADVISER_ID IN
				<foreach collection="adviserIds" item="item" close=")" open="(" separator=",">
					#{item}
				</foreach>
		GROUP BY
			ADVISER_ID,
			CHANNEL_ID,
			BOOK_ID
	</select>


	<select id="getAgentBookStatsDetail" parameterType="map" resultType="com.pcloud.book.book.vo.AgentBookStatsDetailVO">
		SELECT
		tb.bookAdviserId,
		tb.channelId,
		tb.bookName,
		tb.isbn,
		tb.bookId,
		tb.adviserId,
		tb.isRay,
		tb.serialNumber,
		tb.bookGroupId
		FROM (
		SELECT
		ba.BOOK_ADVISER_ID AS bookAdviserId,
		ba.CHANNEL_ID AS channelId,
		b.BOOK_NAME AS bookName,
		b.ISBN AS isbn,
		b.BOOK_ID AS bookId,
		ba.ADVISER_ID AS adviserId,
		IF(ba.is_open_robot_process OR bg.join_group_type = 4, 1, 0) AS isRay,
		b.SERIAL_NUMBER AS serialNumber,
		bg.id AS bookGroupId
		FROM
		book_adviser ba
		LEFT JOIN book b ON ba.BOOK_ID = b.BOOK_ID
		LEFT JOIN book_group bg on ba.BOOK_ID = bg.book_id AND ba.CHANNEL_ID = bg.channel_id AND ba.ADVISER_ID = bg.create_user
		<where>
			ba.ADVISER_ID IN
			<foreach collection="advisers" separator="," close=")" item="it" open="(">
				${it}
			</foreach>
			<if test="raysBookIds != null and raysBookIds.size > 0 or name != null">
			AND (
			<if test="raysBookIds != null and raysBookIds.size > 0">
				<foreach collection="raysBookIds" open="(" item="item" close=")" separator="OR">( 1=1
					<if test="item.adviserId != null">
						AND ba.ADVISER_ID =${item.adviserId}
					</if>
					<if test="item.raysChannelId != null">
						AND ba.CHANNEL_ID=${item.raysChannelId}
					</if>
					<if test="item.raysBookId != null">
						AND ba.BOOK_ID=${item.raysBookId}
					</if>
					)
				</foreach>
				<if test="name != null">
					OR
				</if>
			</if>
			<if test="name != null">
				 (
				b.BOOK_NAME LIKE CONCAT('%',#{name},'%') OR b.ISBN LIKE CONCAT('%',#{name},'%')
				<if test="bookNo != null">
					OR b.BOOK_ID LIKE CONCAT('%',#{bookNo},'%')
				</if>
				)
			</if>
			)
			</if>
		</where>
		) tb
		<if test="isRay != null">
		where tb.isRay = #{isRay}
		</if>
		ORDER BY tb.bookAdviserId DESC
	</select>


</mapper>
